更新所有 NULL 字段 MySQL

Question

我想将一个表中的所有 NULL 字段更新为 0。当然

UPDATE mytable SET firstcol=0 WHERE firstcol IS NULL 

可以完成这项工作。但我想知道是否有比只为每列 c&p 这条线更聪明的解决方案。

Answer 1

您可以执行此操作 - 根据需要对每一列重复：

UPDATE `table1`  SET
    `col1` = IFNULL(col1, 0),
    `col2` = IFNULL(col2, 0);

示例：

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

如果您想通过更改列来更改表结构，以便它们不再接受空值，您可以使用存储过程来完成。以下存储过程查询 INFORMATION_SCHEMA COLUMNS 以获取有关以下内容的信息：给定数据库表中的列。根据该信息，它构建了一个准备好的语句，然后用于更改表结构。您可能需要对其进行调整以满足您的具体要求 - 目前，它会查找未设置 NOT NULL 的 INT 列：

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 ;

示例：

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

以下行显示要执行的命令，如有必要，可以从存储过程中删除：

SELECT @alter_cmd;

Answer 2

您可以将列ALTER更改为NOT NULL DEFAULT 0吗？

您可以按照 MySQL 文档：

您可以在单个 ALTER TABLE 语句中发出多个 ADD、ALTER、DROP 和 CHANGE 子句，并用逗号分隔。这是标准 SQL 的 MySQL 扩展，每个 ALTER TABLE 语句仅允许每个子句之一。

Answer 3

您可能希望将列更改为NOT NULL。

ALTER TABLE your_table MODIFY COLUMN your_field INT NOT NULL;

测试用例：

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);

结果：

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)

Answer 4

并非没有中间技术或光标。您可以使用 DESCRIBE mytable; 获取列名称并循环它们以构建您的 UPDATE 查询。

所以这是可能的。但当你写下这些内容时，你可能已经复制并粘贴了；）

Answer 5

这对我有用！

UPDATE `results`  SET
    column1  = IFNULL(column1,0),
    column2  = IFNULL(column2,'');

这是迈克的答案，但没有左侧列的引号！

注意：如果列的数据类型为int，则如果您尝试将值设置为“0”而不是空字符串

Answer 6

我不相信有；任何对不满足 where 子句的行起作用的语句都会更新您不打算更新的行。杰森的答案是正确的，但是，我认为有点不安全，除非你真的确定这就是你想要的。

Answer 7

ALTER TABLE dataBaseName.tableName
ADD COLUMN columnX INT(20) NULL DEFAULT 1 AFTER columnY;

它执行以下操作

1. 在columnY之后添加一个新列columnX。
2. 将整个列 columnX 的值设置为默认值 1

---

           columnY             columnX 

         | cellValueA     |     1       |        
         | cellValueB     |     1       |
         | cellValueC     |     1       |
         | cellValueD     |     1       |