MySQL检测主键含Unicode字符的查询结果异常求助

问题背景 #

我需要编写查询语句，分析多个表的主键数据，判断其中是否包含Unicode字符，但尝试的几种查询方法都得到了错误结果，现梳理情况并寻求原因分析。

表结构 #

mysql> SHOW CREATE TABLE employee_plain;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee_plain | CREATE TABLE `employee_plain` (
  `emp_id` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `emp_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE employee_unicode;
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                                                                  |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee_unicode | CREATE TABLE `employee_unicode` (
  `emp_id` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `emp_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

数据情况 #

其中employee_unicode表的主键列包含Unicode字符：

mysql> select * from employee_plain;
+------------------------------+----------+------+
| emp_id                       | emp_name | age  |
+------------------------------+----------+------+
| asdasd123                    | abcsd    |   12 |
| fsoiuioujvsdf4               | abvkd    |   13 |
| sdfgjshgjshdfljsfklju4532489 | sdfsdff  |   11 |
+------------------------------+----------+------+
3 rows in set (0.00 sec)

mysql> select * from employee_unicode;
+--------------------------------------------------------------+----------+------+
| emp_id                                                       | emp_name | age  |
+--------------------------------------------------------------+----------+------+
| A ΠΛΦΟΙΚ ΑΕ#1420000000000000000                              | sdfsf    |   11 |
| sdfsdfsf234                                                  | fsdfsd   |   12 |
| ΑΣΕΛ - ΑΦΟΙ. ΣΕΛΙΔΗ Α.Ε.#000000000000000                     | sdfsd    |   13 |
| ΦΩΤΗΣ#10000000000                                            | sdfsdfd  |   14 |
+--------------------------------------------------------------+----------+------+
4 rows in set (0.00 sec)

尝试的错误查询及结果 #

我尝试了三种查询方式，但结果均不符合预期：

查询1：用REGEXP匹配非ASCII字符 #

mysql> SELECT
    ->   TABLE_NAME,
    ->   COLUMN_NAME,
    ->   COLUMN_TYPE,
    ->   IF( COLUMN_NAME REGEXP '[^\x00-\x7F]', 'Contains Unicode', 'No Unicode') AS Unicode_validation
    -> FROM
    ->   information_schema.columns
    -> WHERE
    ->   table_schema = 'amv_testdb' AND
    ->   COLUMN_KEY = 'PRI'
    -> ORDER BY
    ->   TABLE_NAME,
    ->   ORDINAL_POSITION;
+------------------+-------------+--------------+--------------------+
| TABLE_NAME       | COLUMN_NAME | COLUMN_TYPE  | Unicode_validation |
+------------------+-------------+--------------+--------------------+
| employee_plain   | emp_id      | varchar(100) | No Unicode         |
| employee_unicode | emp_id      | varchar(100) | No Unicode         |
+------------------+-------------+--------------+--------------------+
2 rows in set (0.00 sec)

查询2：用CONVERT转ASCII对比 #

mysql> SELECT
    ->   TABLE_NAME,
    ->   COLUMN_NAME,
    ->   COLUMN_TYPE,
    ->   IF( COLUMN_NAME <> CONVERT( COLUMN_NAME USING ASCII), 'No Unicode', 'Contains Unicode') AS Unicode_validation
    -> FROM
    ->   information_schema.columns
    -> WHERE
    ->   table_schema = 'amv_testdb' AND
    ->   COLUMN_KEY = 'PRI'
    -> ORDER BY
    ->   TABLE_NAME,
    ->   ORDINAL_POSITION;
+------------------+-------------+--------------+--------------------+
| TABLE_NAME       | COLUMN_NAME | COLUMN_TYPE  | Unicode_validation |
+------------------+-------------+--------------+--------------------+
| employee_plain   | emp_id      | varchar(100) | Contains Unicode   |
| employee_unicode | emp_id      | varchar(100) | Contains Unicode   |
+------------------+-------------+--------------+--------------------+
2 rows in set (0.00 sec)

查询3：用BINARY转换对比 #

mysql> SELECT
    ->   TABLE_NAME,
    ->   COLUMN_NAME,
    ->   COLUMN_TYPE,
    ->   IF(CONVERT(COLUMN_NAME USING BINARY) <> COLUMN_NAME, 'Contains Unicode', 'No Unicode') AS Unicode_validation
    -> FROM
    ->   information_schema.columns
    -> WHERE
    ->   table_schema = 'amv_testdb' AND
    ->   COLUMN_KEY = 'PRI'
    -> ORDER BY
    ->   TABLE_NAME,
    ->   ORDINAL_POSITION;
+------------------+-------------+--------------+--------------------+
| TABLE_NAME       | COLUMN_NAME | COLUMN_TYPE  | Unicode_validation |
+------------------+-------------+--------------+--------------------+
| employee_plain   | emp_id      | varchar(100) | No Unicode         |
| employee_unicode | emp_id      | varchar(100) | No Unicode         |
+------------------+-------------+--------------+--------------------+
2 rows in set (0.00 sec)

错误原因 #

所有查询出错的核心问题是：你查询的是information_schema.columns中的COLUMN_NAME字段——这是表的列名（比如emp_id），而不是表中存储的实际主键数据。

列名emp_id本身都是ASCII字符，所以不管哪种检测逻辑，都会得到错误的判断结果。你真正需要检查的是每个表中emp_id列的记录值，而非列名。

正确的查询方法 #

单表检测 #

针对单个表，直接查询主键列的记录并检测：

-- 检测employee_plain表的主键是否包含Unicode
SELECT 
  emp_id,
  IF(emp_id REGEXP '[^\x00-\x7F]', '包含Unicode', '无Unicode') AS unicode_check
FROM employee_plain;

-- 检测employee_unicode表的主键是否包含Unicode
SELECT 
  emp_id,
  IF(emp_id REGEXP '[^\x00-\x7F]', '包含Unicode', '无Unicode') AS unicode_check
FROM employee_unicode;

批量检测（动态SQL） #

如果需要批量检测多个表的主键，可以使用动态SQL生成查询（以MySQL为例）：

SET @sql = '';

SELECT GROUP_CONCAT(
  CONCAT(
    'SELECT "', TABLE_NAME, '" AS table_name, emp_id, IF(emp_id REGEXP ''[^\\x00-\\x7F]'', ''包含Unicode'', ''无Unicode'') AS unicode_check FROM ', TABLE_NAME
  ) SEPARATOR ' UNION ALL '
) INTO @sql
FROM information_schema.columns
WHERE table_schema = 'amv_testdb' AND COLUMN_KEY = 'PRI';

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这个动态SQL会自动遍历所有主键列，批量输出每个表的主键记录及Unicode检测结果。

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