Mysql排序问题与information_schema.TABLES

Question

我有一个疑问：

 select distinct name from 
  (
    select TABLE_NAME as name from information_schema.TABLES 
         where TABLE_SCHEMA ='my_db_name' 
     union 
    select distinct db_table as name from status 
  )t 
  order by name

使用mysql (服务器版本: 5.1.44)设置

SHOW VARIABLES LIKE 'character_set%';
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | utf8                                                 |
| character_set_connection | utf8                                                 |
| character_set_database   | utf8                                                 |
| character_set_filesystem | binary                                               |
| character_set_results    | utf8                                                 |
| character_set_server     | utf8                                                 |
| character_set_system     | utf8                                                 |
| character_sets_dir       | /Applications/XAMPP/xamppfiles/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+

SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

表模式：

CREATE TABLE `status` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `db_table` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `rank` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL,
  `style_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `db_table` (`db_table`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

我进一步改变了my.cnf

[mysqld]
#
#
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
#

我删除了数据库并重新创建了它，现在我得到了错误的非法混合排序规则，用于操作'UNION'。请告诉我我做错了什么。我从php和mysql命令行得到了同样的问题。

这样做是可行的：

  select distinct name from 
  (
    select TABLE_NAME collate utf8_unicode_ci as name from information_schema.TABLES 
         where TABLE_SCHEMA ='my_db_name' 
     union 
    select distinct db_table as name from status 
  )t 
  order by name

但是我不喜欢这种调整，我的问题是:对于这个.As是否有适当的修复--您看到mysql全局设置设置为适当的排序规则--但是信息模式仍然在使用utf8_general_ci之类的吗？

Answer 1

如果您检查information_schema的排序规则，您可能会发现utf8_general_ci而不是utf8_unicode_ci。我也不知道原因。

USE information_schema;
SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

除了在查询中定义排序规则之外，另一个选项是将数据库表更改为使用utf8_general_ci。(就个人而言，我更喜欢在查询中指定排序规则)

请记住，每个表上都有一个排序规则设置，可以覆盖您看到的设置(显示诸如“排序规则%”之类的变量；)。如果所有内容都切换到utf8_general_ci，则可能必须为每个语句运行ALTER语句。如果您有许多表，还可以执行mysqldump、查找和替换来更改所有表，然后重新导入。