更新所有 NULL 字段 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3175118/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
update all NULL fields MySQL
提问by Matt Bannert
I'd like to update all NULL fields in one table to 0. Of course
我想将一张表中的所有 NULL 字段都更新为 0。当然
UPDATE mytable SET firstcol=0 WHERE firstcol IS NULL
would do the job. But I wonder if there′s a smarter solution than just c&p this line for every column.
会做的工作。但我想知道是否有比为每一列 c&p 这条线更聪明的解决方案。
采纳答案by Piskvor left the building
Can you just ALTERthe columns to NOT NULL DEFAULT 0?
你可以只ALTER列到NOT NULL DEFAULT 0吗？
You can do this in a single statement, as per MySQL documentation:
根据MySQL 文档，您可以在单个语句中执行此操作：
You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE statement, separated by commas. This is a MySQL extension to standard SQL, which allows only one of each clause per ALTER TABLE statement.
您可以在单个 ALTER TABLE 语句中发出多个 ADD、ALTER、DROP 和 CHANGE 子句，并用逗号分隔。这是标准 SQL 的 MySQL 扩展，它允许每个 ALTER TABLE 语句的每个子句中只有一个。
回答by Mike
You could do this - repeat as necessary for each column:
您可以这样做 - 根据需要对每一列重复：
UPDATE `table1` SET
`col1` = IFNULL(col1, 0),
`col2` = IFNULL(col2, 0);
Example:
例子：
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
`id` int(10) unsigned NOT NULL auto_increment,
`col1` int(10) unsigned,
`col2` int(10) unsigned,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `table1` VALUES
(1, 1, NULL),
(2, NULL, NULL),
(3, 2, NULL),
(4, NULL, NULL),
(5, 3, 4),
(6, 5, 6),
(7, 7, NULL);
UPDATE `table1` SET
`col1` = IFNULL(col1, 0),
`col2` = IFNULL(col2, 0);
SELECT * FROM `table1`;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | 1 | 0 |
| 2 | 0 | 0 |
| 3 | 2 | 0 |
| 4 | 0 | 0 |
| 5 | 3 | 4 |
| 6 | 5 | 6 |
| 7 | 7 | 0 |
+----+------+------+
UPDATE
更新
If you want to alter the table structure by changing columns so that they no longer accept nulls, you could do it with a stored procedure. The following stored procedure queries the INFORMATION_SCHEMA COLUMNSfor information about columns in a given database table. From that information, it builds up a prepared statement which is then used to alter the table structure. You may need to tweak it to suit your exact requirements - at the moment, it looks for INTcolumns which do not have NOT NULLset:
如果您想通过更改列来更改表结构，以便它们不再接受空值，您可以使用存储过程来完成。以下存储过程查询INFORMATION_SCHEMA COLUMNS以获取有关给定数据库表中列的信息。根据该信息，它构建了一个准备好的语句，然后用于更改表结构。您可能需要调整它以满足您的确切要求 - 目前，它会查找INT未NOT NULL设置的列：
delimiter //
DROP PROCEDURE IF EXISTS no_nulls//
CREATE PROCEDURE `no_nulls` (IN param_schema CHAR(255), IN param_table CHAR(255))
BEGIN
SET @alter_cmd = (SELECT CONCAT(
'ALTER TABLE ',
param_table,
GROUP_CONCAT(
' MODIFY COLUMN ',
`column_name`, ' ',
`column_type`,
' NOT NULL'
SEPARATOR ', ')
) AS `sql_cmd`
FROM INFORMATION_SCHEMA.COLUMNS
WHERE `table_schema` = param_schema
AND `table_name` = param_table
AND LCASE(`data_type`) = 'int'
AND LCASE(`is_nullable`) = 'yes');
IF NOT ISNULL(@alter_cmd) THEN
SELECT @alter_cmd;
PREPARE stmt FROM @alter_cmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END//
delimiter ;
Example:
例子：
CREATE TABLE `test`.`table1` (
`id` int(10) unsigned NOT NULL auto_increment,
`col1` int(10) unsigned,
`col2` int(10) unsigned,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CALL no_nulls('test', 'table1');
+----------------------------------------------------------------------------------------------------------------+
| @alter_cmd |
+----------------------------------------------------------------------------------------------------------------+
| ALTER TABLE table1 MODIFY COLUMN col1 int(10) unsigned NOT NULL, MODIFY COLUMN col2 int(10) unsigned NOT NULL |
+----------------------------------------------------------------------------------------------------------------+
SHOW CREATE TABLE `test`.`table1`;
CREATE TABLE `table1` (
`id` int(10) unsigned NOT NULL auto_increment,
`col1` int(10) unsigned NOT NULL,
`col2` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
The following line displays the command that is to be executed, and may be removed from the stored procedure if necessary:
以下行显示要执行的命令，如有必要，可以将其从存储过程中删除：
SELECT @alter_cmd;
回答by Daniel Vassallo
You may want to alter your columns to NOT NULL.
您可能希望将列更改为NOT NULL.
ALTER TABLE your_table MODIFY COLUMN your_field INT NOT NULL;
Test case:
测试用例：
CREATE TABLE nulltable (id INT);
INSERT INTO nulltable VALUES (1);
INSERT INTO nulltable VALUES (2);
INSERT INTO nulltable VALUES (3);
INSERT INTO nulltable VALUES (NULL);
INSERT INTO nulltable VALUES (NULL);
INSERT INTO nulltable VALUES (NULL);
INSERT INTO nulltable VALUES (5);
Result:
结果：
mysql> SELECT * FROM nulltable;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| NULL |
| NULL |
| NULL |
| 5 |
+------+
7 rows in set (0.00 sec)
mysql> ALTER TABLE nulltable MODIFY COLUMN id INT NOT NULL;
Query OK, 7 rows affected, 3 warnings (0.08 sec)
Records: 7 Duplicates: 0 Warnings: 3
mysql> SELECT * FROM nulltable;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 0 |
| 0 |
| 0 |
| 5 |
+----+
7 rows in set (0.00 sec)
回答by Jason McCreary
Not without an intermediate technology or cursor. You could use DESCRIBE mytable;to get the column names and loop over them to build your UPDATEqueries.
并非没有中间技术或光标。您可以使用DESCRIBE mytable;获取列名并遍历它们以构建UPDATE查询。
So it is possible. But by the time it took you to write that, you probably just could have copy and pasted ;)
所以这是可能的。但是到你写那个的时候，你可能只需要复制和粘贴;)
回答by Brian Hooper
I don't believe there is; any statement that worked on rows that didn't satisfy the where clause would update rows you didn't intent to update. Jason's answer is correct, but, I think, a bit unsafe, unless you are really sure that's what you want.
我不相信有；任何处理不满足 where 子句的行的语句都会更新您不打算更新的行。杰森的回答是正确的，但我认为有点不安全，除非你真的确定这就是你想要的。
回答by nilakantha singh deo
ALTER TABLE dataBaseName.tableNameADD COLUMN columnXINT(20) NULL DEFAULT 1 AFTER columnY;
更改表dataBaseName。tableNameADD COLUMN columnXINT(20) NULL DEFAULT 1 AFTER columnY;
It does the following
它执行以下操作
- adds a new column columnX after columnY.
- sets its value to default 1 throughout the column columnX
- 在 columnY 之后添加一个新列 columnX。
- 在整个列 columnX 中将其值设置为默认值 1
columnY columnX
| cellValueA | 1 |
| cellValueB | 1 |
| cellValueC | 1 |
| cellValueD | 1 |
回答by harry
This worked for me!
这对我有用！
UPDATE `results` SET
column1 = IFNULL(column1,0),
column2 = IFNULL(column2,'');
This is mike's answer but without the quotes for columns on the left !
这是迈克的答案，但没有左侧列的引号！
Note:If you are trying to set your values '0' instead of an empty string if a column's datatype is int
注意：如果列的数据类型为int，则尝试将值设置为“0”而不是空字符串
