jQuery DataTables服务端搜索无法识别中文、泰文等亚洲字符

正在开发jQuery DataTables相关功能，基础功能已正常运行，但搜索功能检索非英文字符时存在异常：

• 页面初始加载状态正常
  
• 搜索client_name字段的英文字符时功能正常
  
• 搜索中文、泰文等非英文客户名称时，无法返回匹配结果
  

主HTML文件代码 #

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>JQuery Datatable</title>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css">
    <script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
    <script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function() {
            $('#jquery-datatable-ajax-php').DataTable({
                  'processing': true,
                  'serverSide': true,
                  'serverMethod': 'post',
                'order': [[5, 'desc']],
                  'ajax': {
                      'url':'datatable.php'
                  },
                  'columns': [
                    { data: 'client_name' },
                    { data: 'quote_number' },
                    { data: 'project' },
                    { data: 'status' },
                    { data: 'quote_total', render: $.fn.dataTable.render.number(',', '.', 2,), className: 'dt-body-right'},
                    { data: 'id', 'name': 'id', fnCreatedCell: function (nTd, sData, oData, iRow, iCol) {$(nTd).html("<a class='linkboner' href='/quotes/view/"+oData.id+"?nocache=<?php echo time()?>'>View / Edit</a>");}}
                  ],
                'columnDefs': [ {
                    'targets': 3,
                    'createdCell': function (td, cellData, rowData, row, col) {
                        if ( cellData == 'Pending' ) {
                            $(td).css('color', '#e27522'),
                            $(td).css('font-weight', 'bold')
                        }
                        else if ( cellData == 'Confirmed' ) {
                            $(td).css('color', 'green'),
                            $(td).css('font-weight', 'bold')
                        }
                        else if ( cellData == 'Accepted' ) {
                            $(td).css('color', 'lightgreen'),
                            $(td).css('font-weight', 'bold')
                        }}
                      }]
           });
        } );
    </script>

</head>
<body>
    <div class="container mt-5">
        <h2 style="margin-bottom: 30px;">jQuery Datatable</h2>
        <table id="jquery-datatable-ajax-php" class="display" style="width:100%">
            <thead>
                <tr>
                    <th>Client</th>
                    <th>Quote Number</th>
                    <th>Project</th>
                    <th>Status</th>
                    <th>Amount</th>
                    <th data-orderable="false">Action</th>
                </tr>
            </thead>
        </table>
    </div>
</body>
</html>

datatable.php服务端处理文件代码 #

<?php
   include 'connection.php';
   $draw = $_POST['draw'];
   $row = $_POST['start'];
   $rowperpage = $_POST['length']; // 每页显示行数
   $columnIndex = $_POST['order'][0]['column']; // 排序列索引
   $columnName = $_POST['columns'][$columnIndex]['data']; // 排序列字段名
   $columnSortOrder = $_POST['order'][0]['dir']; // 排序方向 asc/desc
   $searchValue = $_POST['search']['value']; // 搜索关键词
   $searchArray = array();
   // 搜索逻辑
   $searchQuery = " ";
   if($searchValue != ''){
      $searchQuery = " AND (quote_number LIKE :quote_number OR client_name LIKE :client_name OR project LIKE :project  ) ";
      $searchArray = array( 
           'quote_number'=>"%$searchValue%",
           'client_name'=>"%$searchValue%",
           'project'=>"%$searchValue%"     
      );
   }
   // 无过滤总记录数
   $stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM quotes JOIN clients ");
   $stmt->execute();
   $records = $stmt->fetch();
   $totalRecords = $records['allcount'];
   // 带过滤总记录数
   $stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM quotes JOIN clients WHERE 1 ".$searchQuery);
   $stmt->execute($searchArray);
   $records = $stmt->fetch();
   $totalRecordwithFilter = $records['allcount'];
   // 查询列表数据
   $stmt = $conn->prepare("SELECT quotes.*, clients.client_name FROM quotes INNER JOIN clients ON quotes.client_id = clients.id WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");
   

   // 绑定搜索参数
   foreach ($searchArray as $key=>$search) {
      $stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);
   }
   $stmt->bindValue(':limit', (int)$row, PDO::PARAM_INT);
   $stmt->bindValue(':offset', (int)$rowperpage, PDO::PARAM_INT);
   $stmt->execute();
   $empRecords = $stmt->fetchAll();
   $data = array();
   foreach ($empRecords as $row) {
      $data[] = array(
         "id"=>$row['id'],
         "client_name"=>$row['client_name'],
         "quote_number"=>$row['quote_number'],
         "project"=>$row['project'],
         "status"=>$row['status'],
         "quote_total"=>$row['quote_total']
      );
   }
   // 返回响应
   $response = array(
      "draw" => intval($draw),
      "iTotalRecords" => $totalRecords,
      "iTotalDisplayRecords" => $totalRecordwithFilter,
      "aaData" => $data
   );
   echo json_encode($response);
?>

非英文字符搜索失效是字符集配置不匹配导致，按以下步骤逐一修复即可：

1. 前端请求编码校验
   现有HTML已配置<meta charset="utf-8">，基础配置正确。如果浏览器开发者工具中查看POST请求的搜索关键词传到后端时出现乱码，在DataTables的ajax配置中补充编码声明：

   'ajax': {
       'url':'datatable.php',
       'contentType': 'application/x-www-form-urlencoded; charset=UTF-8'
   }
   

2. PHP数据库连接字符集配置（核心故障点）
   现有代码未指定PDO连接的字符集，非英文字符传入SQL查询时会因为字符集不匹配导致LIKE匹配失效。修改connection.php中的PDO连接逻辑，连接初始化后立刻设置字符集为utf8mb4（支持中文、泰文等所有Unicode字符）：

   // 数据库连接后执行
   $conn->exec("SET NAMES utf8mb4");
   

   也可以直接在PDO初始化的DSN中声明字符集：

   $dsn = "mysql:host=数据库地址;dbname=数据库名;charset=utf8mb4";
   $conn = new PDO($dsn, 数据库用户名, 数据库密码);
   

3. MySQL表字段字符集校验
   检查存储客户名、项目名的表和字段字符集，确保为utf8mb4，排序规则推荐使用utf8mb4_unicode_ci，多语言匹配兼容性最好。如果字符集不匹配，执行以下SQL修改：

   ALTER TABLE clients CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   ALTER TABLE quotes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   

4. 分页逻辑bug修复
   现有代码LIMIT参数绑定顺序写反：LIMIT语法后第一个参数为每页条数，第二个参数为查询偏移量，原代码绑定顺序颠倒会导致分页数据异常，修正如下：

   $stmt->bindValue(':limit', (int)$rowperpage, PDO::PARAM_INT);
   $stmt->bindValue(':offset', (int)$row, PDO::PARAM_INT);
   

内容的提问来源于stack exchange，提问作者dss