我正在使用mySQL 5.5.37。我有一个带有varchar(32)ID的桌子
mysql> desc grade;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| ID | varchar(32) | NO | PRI | | |
| NAME | varchar(20) | NO | | NULL | |
| GRADE_ORDER | int(11) | NO | | NULL | |
+-------------+-------------+------+-----+---------+-------+
我正在尝试更新链接到上面ID列的列,但是我会发现一个错误,因为他们抱怨未能找到匹配值...
mysql> update resource r set grade_id = convert(substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1), unsigned integer) where r.description like '% Grade%';
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`my_db`.`resource`, CONSTRAINT `FK3_RESOURCE` FOREIGN KEY (`GRADE_ID`) REFERENCES `grade` (`ID`))
我如何确定脱名值是什么?我注意到我拥有的资源数量匹配了我能够在两个表之间制作的交叉连接数量...
mysql> select count(*) FROM (select convert(substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1), unsigned integer) from resource r where r.description like '% Grade%') q;
+----------+
| count(*) |
+----------+
| 1340 |
+----------+
1 row in set, 7 warnings (0.01 sec)
mysql> select count(*) from resource r, grade g where convert(substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1), unsigned integer) = g.id and r.description like '% Grade%' ;
+----------+
| count(*) |
+----------+
| 1340 |
+----------+
1 row in set, 1431 warnings (0.02 sec)
因此,如果一切都匹配,我如何找出无法更新的内容?
编辑:所有表的所有主要键均为varchar(32)
写一个选择语句,返回返回grade_id
的"新值"的表达式。
SELECT r.description
, SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1) AS foo
FROM resource r
WHERE r.description like '% Grade%'
测试哪些新的grade_id
值中的哪个不匹配grade
中的id
,我们可以使用 anti-join 模式。这是一个外部连接,可以返回resource
的行,以及等级的匹配行,然后排除所有具有匹配的行。从resource
留下的行, grade
中没有匹配行。
SELECT r.description
, SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1) AS foo
, CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1),UNSIGNED) AS fu
FROM resource r
-- anti-join exclude matching rows
LEFT
JOIN grade g
ON g.id = CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1),UNSIGNED)
WHERE g.id IS NULL
AND r.description like '% Grade%'
g.id
的平等比较确保我们表达式不会为匹配行而无效。如果我们排除G.ID的所有非零值,则排除所有具有匹配的行。
最有可能的解释是描述列中的意外数据,或者当找不到匹配时,SUBSTRING_INDEX
功能的意外行为可能。
随访
如果该查询成功执行并返回零行,则每个新的grade_id
值都是与grade
中的id
的匹配。
因此,难题是为什么更新声明违反了外键约束。
我们需要排除用grade_id
弄乱的BEFORE UPDATE ON resource
触发器。一旦我们排除了...
也许是引起问题的隐式数据类型转换。在进行比较之前,让我们将该整数值投入到字符串中。与上面相同的查询,但带有CONVERT( expr ,CHAR(32))
包装器。
SELECT r.description
, SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1) AS foo
, CONVERT(
CONVERT(
SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1)
,UNSIGNED)
,CHAR(32)) AS new_grade_id
FROM resource r
-- anti-join exclude matching rows
LEFT
JOIN grade g
ON g.id
= CONVERT(
CONVERT(
SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1)
,UNSIGNED)
,CHAR(32))
WHERE g.id IS NULL
AND r.description like '% Grade%'
如果仍然返回零行,那么我们可能会怀疑运营问题。就像要分配的新grade_id
值一样,在应用WHERE
子句之前已检查。但这没什么意义。(如果是这样,我会很惊讶。)
作为测试,我会编写一个查询,该查询返回旧的grade_id
值,并将要分配给grade_id
列的新值返回。假设resource
的id
列是主要键:
SELECT r.id
, r.description
, r.grade_id AS old_grade_id
, CONVERT(
CONVERT(
SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1)
,UNSIGNED)
,CHAR(32)) AS new_grade_id
FROM resource r
WHERE r.description like '% Grade%'
如果该查询正在返回我们期望的结果,我们可以将其引用为多桌更新语句中的内联视图,并在id
列上加入。
类似的东西:
UPDATE resource t
JOIN ( SELECT r.id
, r.description
, r.grade_id AS old_grade_id
, CONVERT(
CONVERT(
SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1)
,UNSIGNED)
,CHAR(32)) AS new_grade_id
FROM resource r
WHERE r.description like '% Grade%'
) v
ON v.id = t.id
SET t.grade_id = v.new_grade_id
您将grade_id值转换为无符号整数。它应该是varchar(32)。这也失败了:
update resource r set grade_id = convert(substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1), char(32)) where r.description like '% Grade%';
由于自动类型转换,您的测试查询工作起来。https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html