我可以在 MySQL 中批量将多列设置为 NULL 吗?

Question

>我有一个非常大的数据库，为了测试，我想将一定数量的数据设置为 NULL。

例如，我在 3 个表中有 57 列，所有这些都需要作废。我无法删除行，我只需要知道如果该行存在并且这些字段中没有数据，则一切仍然有效。

为澄清起见，这些字段中的所有数据都已移动到其他表中，并且旧数据未在迁移中擦除。要测试我的报告，我需要知道报告是从新位置而不是旧位置提取的，因为随着新数据的添加，它只会转到新位置。我们的计划是从旧数据库生成每个报告，迁移，然后再次生成它们并进行比较。但是为了确保它们从正确的位置提取，我们希望擦除旧数据，以便它不会提供误报。

有没有办法让我批量执行此操作，或者我应该辞职编写一个又一个逗号分隔的 SET 语句？

Answer 1

您可以使用内部information_schema.COLUMNS表中的数据创建语句。

假设您有此表：

CREATE TABLE my_table (
keep1 INT,
keep2 INT,
set_null1 INT,
set_null2 INT,
set_null3 INT
);

并且您希望将所有列设置为 NULL，除了keep1和keep2.执行以下脚本：

set @db_name = 'test';
set @table_name = 'my_table';
set @exclude_columns = 'keep1,keep2';
select concat(
'UPDATE `', @table_name, '` SETn',
group_concat('`', COLUMN_NAME, '` = NULL' separator ',n'),
';'
)
from information_schema.COLUMNS c
where c.TABLE_SCHEMA = @db_name
and c.TABLE_NAME   = @table_name
and find_in_set(c.COLUMN_NAME, @exclude_columns) = 0;

这将生成以下语句：

UPDATE `my_table` SET
`set_null1` = NULL,
`set_null2` = NULL,
`set_null3` = NULL;

复制结果并将其粘贴到更新脚本中。对所有 12 个表执行此操作，调整变量@db_name、@table_name和@exclude_columns。

请参阅有关 db-fiddle 的演示。

Answer 2

对于 SQL 数据库来说，这是一项非常不寻常的任务，因此它有点尴尬也就不足为奇了。

如您所知，要在 UPDATE 语句中将多列设置为 NULL，您必须单独设置每一列。

UPDATE mytable
SET col1 = NULL, col2 = NULL, ... col57 = NULL
WHERE id = ?;

这可能是相当多的打字。 或者，编写代码以循环访问表中的列名并连接 UPDATE 语句的术语可能是一项任务。轮到你了。

可能更简单的替代方法是删除该行，然后重新插入它，除了主键之外不指定任何值。

DELETE FROM mytable WHERE id = ?;
INSERT INTO mytable SET id = ?;

通过省略其他列，它们将为 NULL，或者采用表中定义的默认值。如果希望那些具有默认值的列也为 NULL，则必须指定该列。

INSERT INTO mytable SET id = ?, col23 = NULL;