MySQL的缺陷/Bug/异常/陷阱/注意事项

这里只是个记录，踩过的坑

内容索引

REGEXP的中文支持

REGEXP 对中文的错误识别，如下语句，结果竟然是1，在 MySQL 5.5.53, MariaDB 5.5.60, MySQL 5.7.24 下测试结果一致.

SELECT '区中医院'  regexp '[一二三四五六七八九十〇]{6,}'  as mt

这个问题是在utf8-general-ci 数据表上做regexp匹配连续的数字汉字时发现，暂时没测试否与字符集的选择相关，猜测是regexp本身行为对宽字符集支持的问题。

已确认 MySQL 8.0.4 以后解决了该 bug

变量/设置选项：sql-mode相关

sql-mode变量默认是空的，这里造成很多问题，强烈建议，至少加入如下的设置。

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

否则，下面的一系列陷阱在等着你…

超过字段长度的字符串会自动截断

插入数据时超过字段长度的字符串会自动截断。这是MySQL的默认行为，很坑很坑；即使这对于初学者来说显得“友好”些，然而它还是个大坑。而且还会因此带来更多诡异行为，比如：按用户名汇总（group by）的统计结果插入另一表，如果不小心目标表用户名字段长度不足、并且设置了惟一键，可能收到报错说重复值（举例统计结果里“阮小二”、“阮小五”、“阮小七”都被截断成“阮小”，就是三条重复数据）。

往表中写入数据中途出错后竟然保留不完整的数据

关于sql-mode的后记

设置了sql-mode，你会用得很开心的。然而，一旦切换到其它环境时，更大的麻烦也可能随之而来。或许只有你把时时刻刻记得这些缺陷，并在每行代码里规避它们。

sql-mode有很多选项，请参考官方手册。

索引统计信息非实时更新，从而会造成索引无效

即索引基数 cardinality ，尤其在对表有大规模写入后容易出现。这个问题比较复杂，在不同版本不同存储引擎下的表现并不一致，8.x的新版本表现似乎明显更好。参看MySQL/MariaDB下索引基数cardinality的更新问题

convert/cast做数据类型转换后结果无法按预期写入

希望从字符串中提取一段数字，如果提取结果为非法数字，使用convert或cast函数强制转换为数字；在select结果记录集里，非数字被转换为0，这是符合预期的；但如果把结果写入字段中示例写入表中，非法数据的写入仍然是失败的，就像写入的是未做过转换的原始值一样。如下示例表，希望从birth字段中提取年份数字，写入到y字段中。

use `test`;
CREATE TABLE IF NOT EXISTS `foo` (
  `id` int(6) NOT NULL,
  `birth` varchar(20) NOT NULL DEFAULT '',
  `y` smallint(6) NOT NULL DEFAULT 0,
  `yt` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `foo` (`id`, `birth`, `y`, `yt`) VALUES
(1, '1945年11月', 0, ''),
(2, 'x年x月', 0, ''),
(3, '１９４０年', 0, ''),
(4, '1962年12月', 0, '');

-- 在select中非法数字转换为0，是符合预期的。
SELECT *,cast(left(birth,locate('年',birth)-1) as UNSIGNED) as x FROM `foo`;

-- 但update到y字段中，就报错了
update foo set y=cast(left(birth,locate('年',birth)-1) as UNSIGNED) ;
#1292 - Truncated incorrect INTEGER value: 'x'

-- 如果不转换直接写入，报错消息是有所不同的
update foo set y=left(birth,locate('年',birth)-1) ;
#1366 - Incorrect integer value: 'x' for column `test`.`foo`.`y` at row 2

Windows下的安装

注册为windows服务时， –install 参数必须写在其他参数前面。如果指定defaults-file 参数，那么 –install 参数后要跟个服务名，可以写成MySQL、MariaDB10或其他名字。

通过zip包升级安装新版本，升级后，要运行 mysql_upgrade.exe 让它升级系统表。如果mysql的root用户有登录密码，需要带上-u -p参数， mysql_upgrade.exe -uroot -pyourpassword

8.x 以后用户授权与此前有重大改变，全新的手工安装后设置root密码将麻烦很多，旧的方式多半已无效。

混用left/right/inner join的查询结果很可能非预期

<TODO>{本节内容似乎有误，未核实}严格来说，这不是mysql的问题。查询优化器会在参与join的表中排出先后次序，这个次序很可能并不是它们在where子句中出现的前后次序，inner join会丢弃无完全匹配的行，而left/right则不是；如果比预期丢早或丢晚了，就很可能造成结果非预期。回想mysql文档中的join都是 xx join (a, b) ON … 这样的写法，甚至较早版本并不支持 xx join a … xx join b… 的写法，可在一定程度上避免类似问题。

~~暂不举实例了。实际应用中已遇到这个问题，实际问题太复杂，等以后有时间再编个小的示例。~~