我想在表中添加新的唯一字段,但在运行命令时
ALTER TABLE b_iblock_element ADD `XML_ID_UNIQUE` INT NOT NULL UNIQUE AFTER `XML_ID`
我有个错误键“xml\u id\u unique”的条目“0”重复!如何忽略此错误?也许mysql中还有其他命令?
snz8szmq1#
如果表中有数据,则需要分三步进行:
-- add the column, with no unique constraintALTER TABLE b_iblock_element ADD `XML_ID_UNIQUE` INT NOT NULL AFTER `XML_ID`;-- assign the values in the column some unique valuesUPDATE b_iblock_element SET XML_ID_UNIQUE = <something unique>;-- add in the unique constraintALTER TABLE b_iblock_element ADD CONSTRAINT unq_XML_ID_UNIQUE UNIQUE (b_iblock_element);``` `<something unique>` 可以指定为:
-- add the column, with no unique constraint
ALTER TABLE b_iblock_element
ADD `XML_ID_UNIQUE` INT NOT NULL AFTER `XML_ID`;
-- assign the values in the column some unique values
UPDATE b_iblock_element
SET XML_ID_UNIQUE = <something unique>;
-- add in the unique constraint
ALTER TABLE b_iblock_element ADD CONSTRAINT unq_XML_ID_UNIQUE UNIQUE (b_iblock_element);
``` `<something unique>` 可以指定为:
UPDATE b_iblock_element CROSS JOIN(SELECT @rn := 0) paramsSET XML_ID_UNIQUE = (@rn := @rn + 1);
但是你可能有其他的赋值方法。
1条答案
按热度按时间snz8szmq1#
如果表中有数据,则需要分三步进行:
UPDATE b_iblock_element CROSS JOIN
(SELECT @rn := 0) params
SET XML_ID_UNIQUE = (@rn := @rn + 1);