Я хотел бы обновить все поля NULL в одной таблице до 0. Конечно
UPDATE mytable SET firstcol=0 WHERE firstcol IS NULL
выполнит эту работу. Но мне интересно, есть ли более разумное решение, чем просто c & p эта строка для каждого столбца.
Я хотел бы обновить все поля NULL в одной таблице до 0. Конечно
UPDATE mytable SET firstcol=0 WHERE firstcol IS NULL
выполнит эту работу. Но мне интересно, есть ли более разумное решение, чем просто c & p эта строка для каждого столбца.
Можете ли вы просто ALTER
столбцы на NOT NULL DEFAULT 0
?
Вы можете сделать это в одном заявлении, согласно Документация по MySQL:
Вы можете выпускать несколько предложений ADD, ALTER, DROP и CHANGE в одном выражении ALTER TABLE, разделенных запятыми. Это расширение MySQL для стандартного SQL, которое допускает только одно из каждого предложения в инструкции ALTER TABLE.
Вы можете сделать это - повторите по мере необходимости для каждого столбца:
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 для получения информации о столбцах в данной таблице базы данных. Из этой информации он создает подготовленный оператор, который затем используется для изменения структуры таблицы. Вам может потребоваться настроить его в соответствии с вашими точными требованиями - на данный момент он ищет столбцы 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 ;
Пример:
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;
Вы можете изменить столбцы на 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)
Не без промежуточной технологии или курсора. Вы можете использовать DESCRIBE mytable;
, чтобы получить имена столбцов и зациклиться на них, чтобы создать ваши запросы UPDATE
.
Так что это возможно. Но к тому времени, когда вам потребовалось написать это, вы, вероятно, просто могли бы скопировать и вставить;)
Я не верю, что есть; любой оператор, который работал над строками, которые не удовлетворяли условию where, обновлял строки, которые вы не намерены обновлять. Ответ Джейсона правильный, но, я думаю, немного небезопасен, если вы действительно не уверены, что вы хотите.
ALTER TABLE dataBaseName
. tableName
ADD COLUMN columnX
INT (20) NULL DEFAULT 1 ПОСЛЕ columnY
;
Он выполняет следующие
columnY columnX
| cellValueA | 1 |
| cellValueB | 1 |
| cellValueC | 1 |
| cellValueD | 1 |