本文来自依云'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 文件上。)
Jan 26, 2015 09:11:18 AM
我个人认为大部分人用mysql纯粹是因为先接触的是mysql,至于依云这里说的这些问题,其实如果愿意的话,也可以找出PostgreSQL的一列清单,不过依然支持依云:),沙发
Jan 26, 2015 12:48:25 PM
不完全是。早期 PostgreSQL 据说性能上比不过 MySQL。而且和 PHP 的发展关系也很大。
至于 PostgreSQL 的坑,你也说了,那个需要「找」。而这些 MySQL 坑,都是不找自来的哦。我之前也使用过 PostgreSQL、MongoDB、Redis,只在 MySQL 上遇到了这么些问题。最近没有发现新的问题了,所以总结一下,写出来,让后来者可以注意一下。
Jan 26, 2015 08:38:40 PM
布尔值,utf8 和 python 这个最让人不爽。
Jan 27, 2015 08:52:14 AM
mysql确实是有问题,而且问题不小,但是依据我自己的实践和我所在单位(全是mysql)的这么多年的实践,文章中的问题从来都每被当成问题过,可能是我们没有能力发现这些问题,作为框架员,我们使用的也几乎全都是django自带的orm,几乎从来不用考虑任何超出框架范围的问题。
Jan 27, 2015 08:24:40 PM
时间戳那个是防止闰秒?我猜的。。
Feb 06, 2015 05:54:22 PM
不错,不过我完全没遇到,哈哈哈哈哈。
Mar 16, 2015 09:14:16 AM
关于“并发删除和更新”这部分,不知是怎么试验得出的结果?
我这开两个 mysql 命令行,分别开启一个事务。
无论是更新还是删除,第二个事务都要等到第一个事务 commit 或 rollback 后才能完成操作,不然就会一直等待下去啊。
postgres 也是同样的情况。
Mar 17, 2015 01:26:50 PM
奇怪,我也不能复现了。可能是有些细节没注意。当时该做个 session 记录的。
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 会在事务一开始时就确定。
Mar 17, 2015 09:24:15 PM
谢谢!比我说的清晰明了多了=w=
Mar 17, 2015 10:20:36 PM
嘿嘿
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.
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
Mar 26, 2015 04:04:16 PM
这些坑mariadb能给填几个?
保守估计一个也没填.
Nov 24, 2015 10:24:13 PM
你用错了,应该使用select for update。简单select是取mvcc的值的,我们称为快照读。select for update取当前值,我们称为当前读。
Nov 24, 2015 10:32:34 PM
其实我觉得MySQL的客户端还算好的。Oracle的才是场灾难。。。他居然不支持上下左右键。。
Nov 25, 2015 12:12:21 PM
那个你 rlwrap 一下?