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, '1940年', 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... 的写法,可在一定程度上避免类似问题。
暂不举实例了。实际应用中已遇到这个问题,实际问题太复杂,等以后有时间再编个小的示例。