从所有表的MySQL数据库中删除所有零日期 [英] Remove all zero dates from MySQL database across all Tables
问题描述
我在MySQL中有很多表,其中的dateTime列0000-00-00 00:00:00
I have plenty of tables in MySQL which which contains zero date in dateTime column 0000-00-00 00:00:00
使用某种管理设置,是否可以禁用零日期并将所有零替换为静态值,例如1-1-1900
?
Using some sort of admin settings, Is it possible to disable zero dates and replace all zero with static value say 1-1-1900
?
我正在进行数据库迁移,其中涉及将100多个MySQL表迁移到SQL Server.
I am working on database migration which involves migrating more than 100 MySQL tables to SQL Server.
我可以通过设置设置来避免在每个表上手动执行脚本吗? 数据库模式?
Can I avoid executing scripts on each table manually by setting up database mode?
推荐答案
要更改现有值,可以使用如下查询:
To change existings values you could use a query like this:
UPDATE tablename SET date_column = '1900-01-01' WHERE date_column = '0000-00-00';
如果要自动执行UPDATE查询,可以使用一条准备好的语句:
If you want to automate the UPDATE query you can use a prepared statement:
SET @sql_update=CONCAT_WS(' ', 'UPDATE', CONCAT(_schema, '.', _table),
'SET', _column, '=', '\'1900-01-01\'',
'WHERE', _column, '=', '\'0000-00-00\'');
PREPARE stmt FROM @sql_update;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
您可以遍历声明为日期的当前模式的所有表中的所有列:
And you can loop through all colums in all tables on the current schema that are declared as date:
SELECT
table_schema,
table_name,
column_name
FROM
information_schema.columns
WHERE
table_schema=DATABASE() AND data_type LIKE 'date%'
要遍历所有列,可以使用存储过程:
To loop through all columns you could use a stored procedure:
DELIMITER //
CREATE PROCEDURE update_all_tables() BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE _schema VARCHAR(255);
DECLARE _table VARCHAR(255);
DECLARE _column VARCHAR(255);
DECLARE cur CURSOR FOR SELECT
CONCAT('`', REPLACE(table_schema, '`', '``'), '`'),
CONCAT('`', REPLACE(table_name, '`', '``'), '`'),
CONCAT('`', REPLACE(column_name, '`', '``'), '`')
FROM
information_schema.columns
WHERE
table_schema=DATABASE() AND data_type LIKE 'date%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
OPEN cur;
columnsLoop: LOOP
FETCH cur INTO _schema, _table, _column;
IF done THEN
LEAVE columnsLoop;
END IF;
SET @sql_update=CONCAT_WS(' ', 'UPDATE', CONCAT(_schema, '.', _table),
'SET', _column, '=', '\'1900-01-01\'',
'WHERE', _column, '=', '\'0000-00-00\'');
PREPARE stmt FROM @sql_update;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP columnsLoop;
CLOSE cur;
END//
DELIMITER ;
请在此处查看示例.
这篇关于从所有表的MySQL数据库中删除所有零日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!