1
25
2015
17

MySQL 之九奇坑

本文来自依云's Blog,转载请注明。

使用了一段时间的 MySQL,体验与使用 PostgreSQL 完全不一样。使用 PostgreSQL 时,「it just works」,而 MySQL 则是「it just doesn't work out of box」。

时间戳与整数

MySQL 有个数据类型叫TIMESTAMP,顾名思义,就是时间戳,支持的时间范围是从UTC 1970年元旦凌晨零点到UTC 2038年元月19日三点14分零七秒。毫无疑问,这是个32位的 UNIX 时间戳。

那么你觉得,当 MySQL 在整数和这样的时间戳之间比较时,会发生什么呢?报错?还是把整数转成时间戳?或者把时间戳转成整数?都不是!MySQL 会首先把整数以十进制转成字符串,然后再把字符串转成时间。也就是,20141028000000这么大的整数,会转成字符串,然后按时间的格式理解,变成2014年10月28日凌晨零点

直接把字符串转成时间没问题。可是,谁会把时间表达成如此奇怪的整数呢?

布尔值

MySQL 有个叫BOOL的类型。可是,它的文档位于数的类型章节之中。而且,它仅仅只是TINYINT(1)的别名!这意味着,MySQL 中的布尔值与整数 0 和 1 是没有分别的。连 Python 都不如,至少 Python 的 bool 是 int 的子类。

于是乎,明明 SQLAlchemy 定义时写的是布尔类型,结果因为表结构是 SQLAlchemy 自动解析的,取出来就变成了整数。

比虚设更讨厌的 CHECK

与上例类似。看起来,MySQL 是支持CHECK约束的。但是不能被表面现象蒙蔽了,文档后面写着「The CHECK clause is parsed but ignored by all storage engines」。只解析,假装自己支持,但是没有作用。这个问题在2007年二月有人报告了。近八年了,依旧如故。

继续骗人:utf8不是 UTF-8

MySQL 似乎从很早开始就支持一个叫utf8的编码了。可是,你往数据库里插入一个「😄」(😄)字符试试?你可以直接在本文后边评论试试。这个字符及其后的字符会消失,因为 MySQL 的utf8只支持 BMP(基本多文种平面)范围内的 Unicode 字符。也就是,MySQL 的utf8使用三字节表达,因此只支持 U+0000 到 U+ffff 范围内的字符。我曾经有篇文章就是因为插入了音调符号而被截断,现在只能小心地使用 HTML 转义形式来写了。

Arch Linux 的 AUR 也使用 MySQL,因此也遇到了字符神秘消失的事件。

如果使用的是 5.5 及以上的版本,可以使用一个 MySQL 称为utf8mb4的字符集,也就是用四字节表达的 UTF-8 编码。明明 UTF-8 是为了统一编码而诞生的,结果又被 MySQL 给分裂成了两个。

binlog 格式

通常,软件会默认一个尽量普适的配置,让大多数人不需要折腾就用着很爽。MySQL 反其道而行之,binlog 默认使用STATEMENT。然后,一不小心使用了它不支持的查询就报错了,让人经过 Google 之后再手动给设置成MIXED格式。

并发删除和更新

MySQL 默认的事务隔离级别是repeatable read,看上去比 PostgreSQL 默认的read committed级别要高。但是呢,有一些很怪异的行为。

两个事务 A 和 B 开始了。A 读取数据库发现 id=1 的记录。B 把 id=1 的记录删除掉(并提交事务)。A 也决定把 id=1 的记录删除。然后 A 再读,咦?怎么 id=1 的记录还在??

PostgreSQL 在read committed级别下,删除也都能成功,但是删除之后是读不到数据了的。

PostgreSQL 在repeatable read级别下,后删除的那个事务会失败。

MySQL 在serializable级别下,后删除的那个事务才会失败。

并发更新时也是这样:

两个事务 A 和 B 同时执行update t set v = v + 1 where id = 2更新数据(假设原数据为 id=2, v=1),会和 PostgreSQL 的read committed级别一样,双方更新均成功。但是,当事务 B 提交之后,A 事务还是看到 v=1。在自己提交之前,自己的更新和其它已提交事务的更新都看不到。

我不知道 MySQL 这样的行为是否符合 SQL 标准。但我知道,它肯定不符合我的直觉:明明我都把数据给改了,为什么我自己都看不到呢?

2015年3月17日更新:安坚实的评论很赞!

糟糕的 Python 客户端库

MySQL 官方 C 库不支持异步,所以使用其的 Python 库完全没办法异步。

oursql 默认会处于自动提交模式,而且很久不更新了。

Oracle 自己弄的 MySQL Connector/Python 倒是没这个问题。但是有其它 N 个问题,比如你得重命名二进制数列的列名,不然会报错:

cursor.execute('select binary %s as a', (b'\xe0\xda\x94\xb8\x89\xf7',))

比如以下查询总是返回空,不管你的数据库里有什么:

cursor.execute('select * from users where token = %s', (bytes_object,))

比如 network.py:226 这里是这么写的:

            packet = bytearray(4)
            read = self.sock.recv_into(packet, 4)
            if read != 4:
                raise errors.InterfaceError(errno=2013)

于是,当你的返回结果很大,导致这里想接收的四字节数据不在同一次recv系统调用中时,就会抛出异常。真不知道写这库的人学过网络编程没,连 short read 都不知道处理。

至于像 PostgreSQL 的连接那样,通过with语句进行自动提交或者回滚就更别想了。

经常误退出的命令行工具

在 MySQL 的交互式命令行里,不小心写了一个反悔了的查询怎么办?比如回车后才发现输出太多了根本没意义,又或者查询里有个地方写错了。我下意识的反应是,按Ctrl-C,中止查询。在按下Ctrl-C的时候,如果查询还没结束,那么查询中止,一切安好。如果不小心慢了 0.01 秒,按键时查询已经执行完毕了呢?MySQL 命令行工具会直接退出(包括 MySQL 官方的,和 MariaDB 的版本),你只能再重新输入密码、重新连接。这是很神奇的事情。我试过了一些别的软件的成熟的交互式命令行工具,比如 bash、zsh、python、ghci、irb、erl,都不会在Ctrl-C时退出。lua、awesome-client 和 rusti 会退出,可前者只使用 ANSI C 的没办法,而后两者并不算成熟。

当然它有一个--sigint-ignore选项,如果你记得加上的话,Ctrl-C时就不会退出了,而是没有任何反应。它都不取消输入到一半的命令。

连接 localhost 等同于连接默认 socket 文件

MySQL 的客户端库,在连接localhost时,或者不指定要连接的主机时,会连接到它默认的 socket 文件。

我有个 MySQL 实例在 3306 端口和默认 socket 文件上监听,另启动了一个实例用于一些测试性工作,监听在 3307 端口和自己指定的 socket 文件上。但是,当不指定-h 127.0.0.1时,即使指定了端口号-P 3307也无济于事。它依旧会连接默认的 socket 文件。我花了很长时间去调试我遇到的问题,直到在 htop 里按了一下l键,看到 MySQL 命令行工具连接的不是我设想的地址才恍然大悟。

指定连接不同的地方当然就应该连接到不同的地方,不然你告诉我我的指定没有生效也好啊。这点 PostgreSQL 就处理得更好。它也是默认连接 socket 文件,即使指定了端口号。但是,指定不同的端口号时它会去连不同的 socket 文件!对于 socket 文件来说,「端口号」其实是文件名后缀,比如/run/postgresql/.s.PGSQL.5433。这样子就不会不小心连错而不自知。(当然 PostgreSQL 也不会自作聪明地在你要连接localhost的时候给连接到 socket 文件上。)

Category: 数据存储 | Tags: mysql 数据库 | Read Count: 16817
菜鸟浮出水 说:
Jan 26, 2015 09:11:18 AM

我个人认为大部分人用mysql纯粹是因为先接触的是mysql,至于依云这里说的这些问题,其实如果愿意的话,也可以找出PostgreSQL的一列清单,不过依然支持依云:),沙发

Avatar_small
依云 说:
Jan 26, 2015 12:48:25 PM

不完全是。早期 PostgreSQL 据说性能上比不过 MySQL。而且和 PHP 的发展关系也很大。

至于 PostgreSQL 的坑,你也说了,那个需要「找」。而这些 MySQL 坑,都是不找自来的哦。我之前也使用过 PostgreSQL、MongoDB、Redis,只在 MySQL 上遇到了这么些问题。最近没有发现新的问题了,所以总结一下,写出来,让后来者可以注意一下。

greatghoul 说:
Jan 26, 2015 08:38:40 PM

布尔值,utf8 和 python 这个最让人不爽。

younger 说:
Jan 27, 2015 08:52:14 AM

mysql确实是有问题,而且问题不小,但是依据我自己的实践和我所在单位(全是mysql)的这么多年的实践,文章中的问题从来都每被当成问题过,可能是我们没有能力发现这些问题,作为框架员,我们使用的也几乎全都是django自带的orm,几乎从来不用考虑任何超出框架范围的问题。

GSHXD 说:
Jan 27, 2015 08:24:40 PM

时间戳那个是防止闰秒?我猜的。。

libraco 说:
Feb 06, 2015 05:54:22 PM

不错,不过我完全没遇到,哈哈哈哈哈。

Avatar_small
安坚实 说:
Mar 16, 2015 09:14:16 AM

关于“并发删除和更新”这部分,不知是怎么试验得出的结果?

我这开两个 mysql 命令行,分别开启一个事务。
无论是更新还是删除,第二个事务都要等到第一个事务 commit 或 rollback 后才能完成操作,不然就会一直等待下去啊。

postgres 也是同样的情况。

Avatar_small
依云 说:
Mar 17, 2015 01:26:50 PM

奇怪,我也不能复现了。可能是有些细节没注意。当时该做个 session 记录的。

Avatar_small
安坚实 说:
Mar 17, 2015 07:39:07 PM

MySQL 和 PG 在 Repeatable Read 级别下确实也有不一样的地方。

并发删除时:
事务A: BEGIN;
事务B: BEGIN;
A: DELETE * FROM test WHERE id = 1; COMMIT;
B: DELETE * FROM test WHERE id = 1;
Result In MySQL: Query OK, 0 rows affected
Result In PG: ERROR: could not serialize access due to concurrent update
虽然结果是一样的,但 MySQL 是以没有行匹配到的形式结束,而 PostgreSQL 是以报错的形式结束。

并发更新时:
A: BEGIN;
B: BEGIN;
# 假设有这样一条记录: id=1, val=5
A: UPDATE test SET val = 10 WHERE id = 1; COMMIT;
B: SELECT val FROM test WHERE id = 1; # 返回 5,这是正常的(因为这里是事务B)
B: UPDATE test SET val = val + 1 WHERE id = 1;
Result In Mysql: Query OK, 1 row affected (0.00 sec) | Rows matched: 1 Changed: 1 Warnings: 0
Result In PG: ERROR: could not serialize access due to concurrent update
此时在 MySQL 下如果在 B 事务中执行 SELECT val FROM test WHERE id = 1; 会返回 11
也就是说事务 A 中对 val 的更新影响到了事务 B。
这在事务 B 内部看来就有点不太和逻辑了:刚刚 SELECT 的结果是 val=5,现在执行 val=val+1,它居然就变成 11 了。
SQL 标准里貌似没有没有相关的定义,所以 MySQL 可能还算不上违反标准,但我是觉得这样很容易造成潜在的问题。

另外,MySQL 和 PostgreSQL 在快照的使用上也有不同
假设有这样一条数据:id=1, val=10
A: BEGIN;
B: BEGIN;
A: UPDATE test SET val = 15 WHERE id = 1; COMMIT;
B: SELECT val FROM test WHERE id = 1; # 在 MySQL 中返回 15,在 PG 中返回 10
MySQL 会在第一次 SELECT 某个表时确定整个事务中要使用的快照,而 PG 会在事务一开始时就确定。

Avatar_small
依云 说:
Mar 17, 2015 09:24:15 PM

谢谢!比我说的清晰明了多了=w=

Avatar_small
Wayne 说:
Mar 18, 2015 06:25:51 PM

我手头没有MySQL和PG,不过看你这里的描述,我感觉有个地方不太对……

你的最后一个测试结果中说 “MySQL 和 PostgreSQL 在快照的使用上也有不同”,那么根据这一点,在第二个测试中,

A: UPDATE test SET val = 10 WHERE id = 1; COMMIT;
B: SELECT val FROM test WHERE id = 1; # 返回 5,这是正常的(因为这里是事务B)

这里,MySQL中的B应当返回10,而不是5.

Avatar_small
安坚实 说:
Mar 18, 2015 06:33:29 PM

哦,不好意思,写的时候没注意。

并发更新的例子应该是这样:
A: BEGIN;
B: BEGIN;
# 假设有这样一条记录: id=1, val=5
A: UPDATE test SET val = 10 WHERE id = 1;
B: SELECT val FROM test WHERE id = 1; # 返回 5,这是正常的(因为这里是事务B)
B: UPDATE test SET val = val + 1 WHERE id = 1; # 此时事务B进入等待状态,要等事务A提交或回滚才能完成操作
A: COMMIT;
B: Result In Mysql: Query OK, 1 row affected (0.00 sec) | Rows matched: 1 Changed: 1 Warnings: 0
B: Result In PG: ERROR: could not serialize access due to concurrent update

Avatar_small
lispji 说:
Mar 26, 2015 04:04:16 PM

这些坑mariadb能给填几个?
保守估计一个也没填.

kamushin 说:
Nov 24, 2015 10:24:13 PM

你用错了,应该使用select for update。简单select是取mvcc的值的,我们称为快照读。select for update取当前值,我们称为当前读。

kamushin 说:
Nov 24, 2015 10:32:34 PM

其实我觉得MySQL的客户端还算好的。Oracle的才是场灾难。。。他居然不支持上下左右键。。

Avatar_small
依云 说:
Nov 25, 2015 12:12:21 PM

那个你 rlwrap 一下?


登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter

部分静态文件存储由又拍云存储提供。 | Theme: Aeros 2.0 by TheBuckmaker.com