mysql 按日期删除数据库,从所有表的MySQL数据库中删除所有零日期

I have plenty of tables in MySQL which which contains zero date in dateTime column 0000-00-00 00:00:00

Using some sort of admin settings, Is it possible to disable zero dates and replace all zero with static value say 1-1-1900?

EDIT:

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

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 ;

Please see an example here.