MySQL - 索引创建问题 - 区别在哪里？

Question

我有两个 MySQL 数据库实例:TST 和 DEV。

尖沙咀:

mysql --version
mysql  Ver 14.14 Distrib 5.6.40, for Linux (x86_64) using  EditLine wrapper

开发:

mysql --version
mysql  Ver 14.14 Distrib 5.6.41, for Linux (x86_64) using  EditLine wrapper

两种环境上的版本几乎相同。

两个实例上的数据库具有相同的架构和相同的数据。

有问题的表的结构如下:

CREATE TABLE `searchItem` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `dataType` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `value` varchar(1024) COLLATE utf8_unicode_ci NOT NULL,
  `creationTime` datetime NOT NULL,
  `modificationTime` datetime NOT NULL,
  `contentId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_36F9E76573A18A3B` (`contentId`),
  KEY `content_type` (`contentId`,`type`),
  CONSTRAINT `FK_36F9E76573A18A3B` FOREIGN KEY (`contentId`) REFERENCES `content` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23518 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

现在我想在列值(1024)上添加索引。我知道在我的例子中索引大小限制是 255。

关于开发:

CREATE INDEX value_type ON searchItem (value, type);
Query OK, 0 rows affected, 1 warning (0.30 sec)

现在我有:

CREATE TABLE `searchItem` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `dataType` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `value` varchar(1024) COLLATE utf8_unicode_ci NOT NULL,
  `creationTime` datetime NOT NULL,
  `modificationTime` datetime NOT NULL,
  `contentId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_36F9E76573A18A3B` (`contentId`),
  KEY `content_type` (`contentId`,`type`),
  KEY `value_type` (`value`(255),`type`),
  CONSTRAINT `FK_36F9E76573A18A3B` FOREIGN KEY (`contentId`) REFERENCES `content` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23518 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

因此它会自动将索引削减到最大大小(255)，这对我来说是预期的行为。

但是在尖沙咀:

CREATE INDEX value_type ON searchItem (value, type);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

我在 my.cnf 文件中没有发现任何差异。

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[isamchk]
key_buffer_size = 16M

[mysqld]
basedir = /usr
bind-address = 0.0.0.0
character-set-server = utf8
datadir = /var/lib/mysql
default_storage_engine = InnoDB
expire_logs_days = 10
init-connect = SET NAMES utf8
innodb_file_per_table = 1
key_buffer_size = 16M
log-error = /var/log/mysql/error.log
max_allowed_packet = 128M
max_binlog_size = 100M
max_connections = 151
myisam_recover = BACKUP
pid-file = /var/run/mysqld/mysqld.pid
port = 3306
query_cache_limit = 1M
query_cache_size = 16M
skip-external-locking
socket = /var/run/mysqld/mysqld.sock
ssl = false
ssl-ca = /etc/mysql/cacert.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem
thread_cache_size = 8
thread_stack = 256K
tmpdir = /tmp
user = mysql

[mysqld_safe]
log-error = /var/log/mysql/error.log
nice = 0
socket = /var/run/mysqld/mysqld.sock

[mysqldump]
max_allowed_packet = 128M
quick
quote-names

编辑:

问题要点:

关于开发:

CREATE INDEX value_type ON searchItem (value, type);
Query OK, 0 rows affected, 1 warning (0.30 sec)

但是在尖沙咀:

CREATE INDEX value_type ON searchItem (value, type);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

问题是为什么它在不同环境下的表现不同？是否有任何配置参数可以对此负责？

Answer 1

这是 Strict SQL Mode 的效果:

As of MySQL 5.6.11, strict mode produces an error for attempts to create a key that exceeds the maximum key length. Previously, this resulted in a warning and truncation of the key to the maximum key length (the same as when strict mode is not enabled).

如果没有严格模式，MySQL可以调整值以使其适合，然后将错误减少为警告；这是您运行该语句时收到的警告。

您可以使用 select @@sql_mode 检查当前的 sql 模式(TST-server 可能会包括 STRICT_TRANS_TABLES，而 >DEV 没有)。该设置可以在运行时全局和每个 session 期间更改，因此某人或某物可能已更改它。