4 个 MySQL 陷阱

由 Mux 赞助的 DEV 全球展示挑战赛：展示你的项目！

原文发布于michaelzanggl.com。订阅我的电子报，不错过任何新内容。

注：其中一些可能也存在于其他数据库系统中。

utf8 实际上并不是 utf8。

你遇到过这种 SQL 异常吗？Incorrect string value: ‘\xF0\x9F\x98\x83 <…’ for column...

你可能和很多人一样，也遇到了同样的问题。原来 MySQL 的 UTF-8 字符集只能存储大约 6% 的 Unicode 代码点。例如，表情符号就被排除在外了。

insert into emails(body) values ('🦄🦄🦄');

所以你应该改用utf8mb4其他方法。

阅读更多相关信息以及如何迁移：https://mathiasbynens.be/notes/mysql-utf8mb4

varchar_field与false0相比

想象一下以下这个人为设计的例子。

function normalizeEmail(email) {
    if (!validate(email)) {
        return false
    }

    return normalize(email)
}

// somewhere in the code
await User.where('email', normalizeEmail(email)).first()

假想的 ORM 将执行查询select * from users where email = <prepared value> LIMIT 1

如果内部验证normalizeEmail成功，则查询将为select * from users where email = 'normalized.email@test.com' LIMIT 1：

如果验证不成功，则查询将为select * from users where email = false LIMIT 1：

现在请select * from users where <varchar field> = false在您的数据库系统中运行类似这样的命令。

由于这些字段不具有可比性，MySQL 会将一个字段转换为另一个字段，使它们匹配并返回所有用户。而我们的 ORM 会直接选择第一个用户并继续执行后续逻辑。😬 这很危险。

同样的情况也发生在field = 0

insert on duplicate key update创建主要钥匙孔

假设我们有一个表格statistics，其列为id（AI）fkid，，title。

INSERT INTO statistics (fkid, title) VALUES (1, 'first');

这将插入一条新记录，其值为id1。假设有一个批处理作业会持续插入或更新标题。它可能会随着时间的推移执行以下查询：

INSERT INTO statistics (fkid, title) VALUES (1, 'second') ON DUPLICATE KEY UPDATE title = 'second';
INSERT INTO statistics (fkid, title) VALUES (1, 'third') ON DUPLICATE KEY UPDATE title = 'third';
INSERT INTO statistics (fkid, title) VALUES (1, 'fourth') ON DUPLICATE KEY UPDATE title = 'fourth';

最后，我们要插入一条具有新 fkid 的记录。

INSERT INTO statistics (fkid, title) VALUES (100, 'first') ON DUPLICATE KEY UPDATE title = 'first';

这会向表中插入一条新记录，但你猜猜它的值id是多少？你可能认为它是 2，但实际上，每次insert on duplicate key update插入失败并处理该记录时update，它都会在内部递增自增值。这意味着id这条记录的值会是 5。

ID 是否真正按顺序排列并不太重要，但你可能会想知道这是为什么。

了解更多信息：https://stackoverflow.com/questions/38347110/prevent-innodb-auto-increment-on-duplicate-key

int(2) 的意思并非你所想的那样。

MySQL 中 INT 类型的长度限制并没有实际意义，你仍然可以插入像 9999999 这样的值。它们只是限制了命令行客户端中显示的字符数。🤨

了解更多信息：https://stackoverflow.com/questions/5634104/what-is-the-size-of-column-of-int11-in-mysql-in-bytes

你还知道其他陷阱吗？