服务器端处理DataTable中的UTF

服务器端处理DataTable中的UTF-8搜索(Server-side processing UTF-8 search in DataTables)

我使用DataTables进行服务器端处理

$('#usersTable').DataTable( { responsive: true, "pageLength": 20, "processing": true, "serverSide": true, "bLengthChange": true, "bSort" : false, "bInfo" : false, "aLengthMenu": [[20, 50, 75, -1], [20, 50, 75, "ყველა"]], "ajax": "helpers/server_processing.php" } );

我也将ssp.class.php中的db连接ssp.class.php为

$db = @new PDO( "mysql:host={$sql_details['host']};dbname={$sql_details['db']}", $sql_details['user'], $sql_details['pass'], array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'" ) );

尽管如此，我仍然在搜索栏中搜索UTF-8字符时遇到问题

任何人都可以帮忙吗？

查询 （从评论复制）

SELECT COUNT(`id`) FROM `users` WHERE (`id` LIKE :binding_0 OR `mac` LIKE :binding_1 OR `ip` LIKE :binding_2 OR `sname` LIKE :binding_3 OR `login` LIKE :binding_4 OR `tariff_plan_id` LIKE :binding_5 OR `now_playing_type` LIKE :binding_6 OR `now_playing_content` LIKE :binding_7 OR `now_playing_start` LIKE :binding_8 OR `keep_alive` LIKE :binding_9 OR `id` LIKE :binding_10 OR `status` LIKE :binding_11 ) SELECT COUNT(`id`) FROM `users` SELECT `id`, `mac`, `ip`, `sname`, `login`, `tariff_plan_id`, `now_playing_type`, `now_playing_content`, `now_playing_start`, `keep_alive`, `id`, `status` FROM `users` WHERE (`id` LIKE :binding_0 OR `mac` LIKE :binding_1 OR `ip` LIKE :binding_2 OR `sname` LIKE :binding_3 OR `login` LIKE :binding_4 OR `tariff_plan_id` LIKE :binding_5 OR `now_playing_type` LIKE :binding_6 OR `now_playing_content` LIKE :binding_7 OR `now_playing_start` LIKE :binding_8 OR `keep_alive` LIKE :binding_9 OR `id` LIKE :binding_10 OR `status` LIKE :binding_11 ) ORDER BY id ASC LIMIT 0, 20

编辑：其他信息

我发现使用utf-8值的LIKE查询不适用于mysql中的DateTime字段

但DataTables会比较每个字段以自动搜索字符串。 有没有简单的方法，我可以告诉DataTables不要搜索DateTime类型的列，如果字符串包含UTF-8字符？

I use DataTables with server-side processing

$('#usersTable').DataTable( { responsive: true, "pageLength": 20, "processing": true, "serverSide": true, "bLengthChange": true, "bSort" : false, "bInfo" : false, "aLengthMenu": [[20, 50, 75, -1], [20, 50, 75, "ყველა"]], "ajax": "helpers/server_processing.php" } );

I also changed db connection in ssp.class.php to

$db = @new PDO( "mysql:host={$sql_details['host']};dbname={$sql_details['db']}", $sql_details['user'], $sql_details['pass'], array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'" ) );

Still I'm having problems while trying to search UTF-8 characters in search bar

can anybody help?

The queries (copied from a comment)

SELECT COUNT(`id`) FROM `users` WHERE (`id` LIKE :binding_0 OR `mac` LIKE :binding_1 OR `ip` LIKE :binding_2 OR `sname` LIKE :binding_3 OR `login` LIKE :binding_4 OR `tariff_plan_id` LIKE :binding_5 OR `now_playing_type` LIKE :binding_6 OR `now_playing_content` LIKE :binding_7 OR `now_playing_start` LIKE :binding_8 OR `keep_alive` LIKE :binding_9 OR `id` LIKE :binding_10 OR `status` LIKE :binding_11 ) SELECT COUNT(`id`) FROM `users` SELECT `id`, `mac`, `ip`, `sname`, `login`, `tariff_plan_id`, `now_playing_type`, `now_playing_content`, `now_playing_start`, `keep_alive`, `id`, `status` FROM `users` WHERE (`id` LIKE :binding_0 OR `mac` LIKE :binding_1 OR `ip` LIKE :binding_2 OR `sname` LIKE :binding_3 OR `login` LIKE :binding_4 OR `tariff_plan_id` LIKE :binding_5 OR `now_playing_type` LIKE :binding_6 OR `now_playing_content` LIKE :binding_7 OR `now_playing_start` LIKE :binding_8 OR `keep_alive` LIKE :binding_9 OR `id` LIKE :binding_10 OR `status` LIKE :binding_11 ) ORDER BY id ASC LIMIT 0, 20

EDIT: additional information

I discovered that LIKE queries with utf-8 values doesn't work with DateTime field in mysql

but DataTables compare every field to search string automatically. Is there an easy way I can tell DataTables not to search DateTime type columns if string contains utf-8 characters?

最满意答案

使用new PDO('dblib:host=host;dbname=db;charset=UTF8', $user, $pwd); 而不是ATTR_INIT_COMMAND 。

->set_charset用于mysqli接口，而不是PDO 。

根据图像，这些列是CHARACTER SET utf8 COLLATION utf8_general_ci ，这对格鲁吉亚人来说是足够的。

向我们展示正在抱怨的LIKE的查询。

这个问题与LIKE语句有关。 如果它在一个Stored Routine中，让我们看看SHOW CREATE ... ; 创建时可能错误的charset / collat​​ion有效。

正如它在“最佳实践”部分所说的那样，“HTML表单应该开始像”。 检查那里的其他物品。

I fixed the issue by altering filter function of ssp.class.php to exlude DateTime columns from search if search string is utf-8

static function filter ( $request, $columns, &$bindings ) { $globalSearch = array(); $columnSearch = array(); $dtColumns = self::pluck( $columns, 'dt' ); if ( isset($request['search']) && $request['search']['value'] != '' ) { $str = $request['search']['value']; for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) { $requestColumn = $request['columns'][$i]; $columnIdx = array_search( $requestColumn['data'], $dtColumns ); $column = $columns[ $columnIdx ]; //**ADDED THIS** if(mb_detect_encoding($request["search"]["value"])=="UTF-8"){ if($column['db']=="keep_alive" || $column['db']=="now_playing_start"){ continue; } } if ( $requestColumn['searchable'] == 'true' ) { $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR ); $globalSearch[] = "`".$column['db']."` LIKE ".$binding; } } } // Individual column filtering if ( isset( $request['columns'] ) ) { for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) { $requestColumn = $request['columns'][$i]; $columnIdx = array_search( $requestColumn['data'], $dtColumns ); $column = $columns[ $columnIdx ]; $str = $requestColumn['search']['value']; if ( $requestColumn['searchable'] == 'true' && $str != '' ) { $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR ); $columnSearch[] = "`".$column['db']."` LIKE ".$binding; } } } // Combine the filters into a single string $where = ''; if ( count( $globalSearch ) ) { $where = '('.implode(' OR ', $globalSearch).')'; } if ( count( $columnSearch ) ) { $where = $where === '' ? implode(' AND ', $columnSearch) : $where .' AND '. implode(' AND ', $columnSearch); } if ( $where !== '' ) { $where = 'WHERE '.$where; } return $where; }