依云'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 文件上。）

使用 PostgreSQL 的命名游标对查询结果进行分页，相对于 OFFSET+LIMIT 查询，相当于保留了每个查询结果，避免了在翻页时的重复计算。^{1, 2, 3}

这是我自己的测试结果。数据量不大，这个测试用的结果集才 20 条结果，所以效果不太明显。（好吧，其实我这边目前的数据量也没必要用现在这个复杂的方案。只是尝试新东西而已啦 ^_^）

In [m]: %%timeit
   ....: for i in range(10):
   ....:   c.execute(sql_c, (sql_m, i*2, 2))
   ....:   list(c)
   ....: 
100 loops, best of 3: 9.83 ms per loop

In [n]: %%timeit
   ....: for i in range(10):
   ....:   c.execute(sql_m.replace('%', '%%') + ' offset %s limit %s', (i*2, 2))
   ....:   list(c)
   ....: 
10 loops, best of 3: 19.8 ms per loop

我使用了一个 PostgreSQL 函数来创建或者复用 cursor。此函数输入参数有：查询语句、位置偏移、获取的数量。这个函数会检查是否已经存在对应的 cursor，如果没有就把查询语句的 md5 值加前缀「p」作为名字。查询语句当然是程序拼接的，不会有人工输入的那种意义相同但是某些写法不一样造成的不同。

PostgreSQL cursor 有两个很重要的特性。其一，它的内容不会随着数据的更新而更新。所以，在相关数据更新之后，已经创建的 cursor 的数据就陈旧了。我创建了一个创建触发器的函数以便清理陈这些旧的 cursor。另外，cursor 是会占用内存或者磁盘空间的，因此要清理掉长期不使用的 cursor。为此，我维护了一张记录 cursor 最后使用时间的表，以及一个清理函数。

PostgreSQL cursor 特性之二：即使指定了WITH HOLD，cursor 的生存期也只在当前会话（连接），并且只在当前会话中看得到。所以，清理函数cleanupCursors还需要将没有记录的 cursor 清除。

CREATE OR REPLACE FUNCTION createCursorTable(name text) RETURNS void AS $$
BEGIN
  EXECUTE format('CREATE TABLE IF NOT EXISTS %I (
    name text UNIQUE,
    last_used TIMESTAMP WITH TIME ZONE default current_timestamp
  )', name);
  EXECUTE format('CREATE INDEX ON %I (last_used)', name);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION createTriggerFor(tname text, cname text) RETURNS void AS $$
BEGIN
  EXECUTE format($f$
    CREATE TRIGGER %I
    AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON %I
    FOR EACH STATEMENT
    EXECUTE PROCEDURE cleanupTriggerFunc (%L)
  $f$, 'cleanupCursorForTable_' || tname || '_' || cname,
  tname, cname);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION dropTriggerFor(tname text, cname text) RETURNS void AS $$
BEGIN
  EXECUTE format($f$ DROP TRIGGER %I on %I $f$,
    'cleanupCursorForTable_' || tname || '_' || cname, tname);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION cleanupTriggerFunc() RETURNS TRIGGER AS $$
DECLARE
  cname text := TG_ARGV[0];
BEGIN
  EXECUTE format('SELECT cleanupCursors(%L, 0)', cname);
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION fetchFromCursor(tname text, query text, off integer, size integer)
RETURNS SETOF record AS $$
DECLARE
  cname text := 'p' || md5(query);
  need_update boolean := false;
BEGIN
  PERFORM name FROM pg_cursors WHERE name = cname;
  IF NOT FOUND THEN
    EXECUTE format('DECLARE %I SCROLL CURSOR WITH HOLD FOR ', cname) || query;
    RAISE NOTICE 'new cursor % created', cname;
    BEGIN
      EXECUTE format('INSERT INTO %I (name) VALUES (%L)', tname, cname);
    EXCEPTION
      WHEN unique_violation THEN
        need_update := true;
    END;
  ELSE
    need_update := true;
  END IF;

  IF need_update THEN
    EXECUTE format('UPDATE %I SET last_used = current_timestamp WHERE name = %L',
      tname, cname);
  END IF;

  EXECUTE format('MOVE ABSOLUTE ' || off || ' FROM %I', cname);
  RETURN QUERY EXECUTE format('FETCH ' || size || ' FROM %I', cname);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION cleanupCursors(tname text, timeout real) RETURNS integer AS $$
DECLARE
  c record;
  i integer := 0;
BEGIN
  FOR c IN
    EXECUTE format($f$
      SELECT name FROM %I
      WHERE extract('epoch' from current_timestamp - last_used) > %L
    $f$, tname, timeout)
  LOOP
    PERFORM name FROM pg_cursors WHERE name = c.name;
    IF FOUND THEN
      RAISE NOTICE 'closing cursor %', c.name;
      EXECUTE format('CLOSE %I', c.name);
    END IF;
    RAISE NOTICE 'clean up record for cursor %', c.name;
    EXECUTE format($f$ DELETE FROM %I WHERE name = %L$f$, tname, c.name);
    i := i + 1;
  END LOOP;

  FOR c IN
    EXECUTE format($f$
      SELECT name FROM pg_cursors WHERE name NOT IN (
        SELECT name FROM %I
      ) AND length(name) = 33 AND substring(name for 1) = 'p'
    $f$, tname)
  LOOP
    RAISE NOTICE 'closing cursor % not present in table %', c.name, tname;
    EXECUTE format('CLOSE %I', c.name);
    i := i + 1;
  END LOOP;

  RETURN i;
END;
$$ LANGUAGE plpgsql;

使用时需要经常去调用下cleanupCursors函数。

PostgreSQL 函数还有这么一个特性，当函数返回setof record时，PostgreSQL 不知道怎么解读那些 record。所以用fetchFromCursor函数时得明确指定获取结果的行类型：

select * from fetchFromCursor('cursors', $$select name from users where name like 'a%' order by last_login_time$$, 0, 10) as f(name text);

有点麻烦。