使用 UUID 插入时 MySQL 外键失败

Question

我有一张表app_user:

CREATE TABLE app_user (
  id            BINARY(16) NOT NULL,
  email_address VARCHAR(255),
  password      VARCHAR(255),
  username      VARCHAR(255),
  role          VARCHAR(255),
  credits       INTEGER,
  PRIMARY KEY (id)
);

我还有一个表 played_game_round，它引用具有外键约束的 app_user 的 id:

CREATE TABLE played_game_round (
  id                  BINARY(16) NOT NULL,
  game_shots          LONGTEXT,
  game_picture_set_id BINARY(16),
  user_id             BINARY(255),
  PRIMARY KEY (id)
);

如果我对 played_game_round 表执行 INSERT，我会遇到外键冲突:

Cannot add or update a child row: a foreign key constraint fails (`myappdb`.`played_game_round`, CONSTRAINT `FK_67s32eu4d5d1m18ub5brp5fk2` 
FOREIGN KEY (`user_id`) REFERENCES `app_user` (`id`))

但我确信这个id是正确的。

这是 show engine innodb status 显示的内容:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2016-02-04 15:05:52 0x700000d51000 Transaction:
TRANSACTION 10052, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
6 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 123145316274176, query id 1698 localhost 127.0.0.1 root
INSERT INTO myappdb.played_game_round (game_picture_set_id, game_shots, user_id, id) VALUES (uuid_to_bin("f63b99f0-9f33-46dc-8a30-e716394f44e7"), "bla", (SELECT id from app_user where id = uuid_to_bin("9d025d10-4fe1-4af1-a361-e91852f00733")), uuid_to_bin("37f3ec14-c65c-4603-b1d2-04bb801b24f1"))
Foreign key constraint fails for table `myappdb`.`played_game_round`:
,
  CONSTRAINT `FK_67s32eu4d5d1m18ub5brp5fk2` FOREIGN KEY (`user_id`) REFERENCES `app_user` (`id`)
Trying to add in child table, in index FK_67s32eu4d5d1m18ub5brp5fk2 tuple:
DATA TUPLE: 2 fields;
 0: len 255; hex 9d025d104fe14af1a361e91852f007330000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000; asc   ] O J  a  R  3                                                                                                                                                                                                                                               ;;
 1: len 16; hex 37f3ec14c65c4603b1d204bb801b24f1; asc 7    \F       $ ;;

But in parent table `myappdb`.`app_user`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 16; hex 9d025d104fe14af1a361e91852f00733; asc   ] O J  a  R  3;;
 1: len 6; hex 000000002722; asc     '";;
 2: len 7; hex ba0000012e0110; asc     .  ;;
 3: len 16; hex 706c6179657240676d61696c2e636f6d; asc player@gmail.com;;
 4: len 6; hex 706c61796572; asc player;;
 5: len 6; hex 706c61796572; asc player;;
 6: len 6; hex 504c41594552; asc PLAYER;;
 7: len 4; hex 80000032; asc    2;;

我已经从我的 Java 代码中进行了测试，并且还在 MySQL 控制台中重现了它(使用来自 https://gist.github.com/damienb/159151 的 uuid_to_bin() 函数)。

可能是什么问题？

Answer 1

在搜索了几个小时后发布后 4 分钟找到了自己:

user_id列被定义为 BINARY(255) , 但应该是 BINARY(16)喜欢 id app_user 的专栏表。