mysql语句里面collate_mysql-utf8_bin中字段上的SQL语句中的COLLATE比使...

两种情况：

使用默认排序规则：

CREATE TABLE IF NOT EXISTS `table` (

`name` varchar(255) collate utf8_general_ci NOT NULL,

UNIQUE KEY `name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT `name` FROM `table` ORDER BY `name`;

使用COLLATE：

CREATE TABLE IF NOT EXISTS `table` (

`name` varchar(255) collate utf8_bin NOT NULL,

UNIQUE KEY `name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT `name` FROM `table` ORDER BY `name` COLLATE utf8_general_ci;

我需要从第一种情况更改为第二种情况,因为索引不区分大小写.仍然订购很重要. utf8_general_cs属于实验性归类,但需要特殊编译.

这会对性能产生影响吗？

在我看来,如果MySQL在utf8中内部存储文本字段而与归类无关,则它不会影响性能.

编辑：

如果使用COLLATE,则说明的输出与不使用时的说明相同.

mysql> EXPLAIN SELECT *

-> FROM `table`

-> ORDER BY `name`

-> COLLATE utf8_general_ci;

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

| 1 | SIMPLE | table | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT *

-> FROM `table`

-> ORDER BY `name`;

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

| 1 | SIMPLE | table | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

1 row in set (0.00 sec)