Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- UPDATE tablename SET date_column = '1900-01-01' WHERE date_column = '0000-00-00';
- 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;
- SELECT
- table_schema,
- table_name,
- column_name
- FROM
- information_schema.columns
- WHERE
- table_schema=DATABASE() AND data_type LIKE 'date%'
- 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 ;
- update your_table
- set date_column = '1900-01-01'
- where date_column = '0000-00-00'
- ALTER TABLE your_table
- CHANGE date_column date_column date NOT NULL DEFAULT '1900-01-01'
- SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
- WHERE COLUMN_NAME='date' AND TABLE_SCHEMA='<YOUR DB NAME>'
- SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
- WHERE COLUMN_NAME in ('timestamp','date','datetime')
- AND TABLE_SCHEMA='<YOUR DB NAME>'
- @string = CONCAT("UPDATE ", @table_name, " SET ", @column_name, "='1-1-1900' WHERE ", @column_name, "=0000-00-00 00:00:00");
- ALTER TABLE `test_table`
- CHANGE COLUMN `created_dt` `created_dt` date NOT NULL DEFAULT '1900-01-01';
- update test_table
- set created_dt= '1900-01-01'
- where created_dt= '0000-00-00'
- #!/usr/bin/perl
- use strict;
- use warnings;
- use DBI;
- my $user='geheim';
- my $pass='secret';
- my $dbh = DBI->connect( "dbi:mysql:host=localhost:database=to_convert:port=3306", $user, $pass ) or die $DBI::errstr;
- # Prints out all the statements needed, might be checked before executed
- my @tables = @{ $dbh->selectall_arrayref("show tables") };
- foreach my $tableh ( @tables){
- my $tabname = $tableh->[0];
- my $sth=$dbh->prepare("explain $tabname");
- $sth->execute();
- while (my $colinfo = $sth->fetchrow_hashref){
- if ($colinfo->{'Type'} =~ /date/i && $colinfo->{'Null'} =~ /yes/i){
- print ("update `$tabname` set `" . $colinfo->{'Field'} . "` = '1990-01-01' where `" . $colinfo->{'Field'} . "` IS NULL; n");
- print ("alter table `$tabname` change column `" . $colinfo->{'Field'} . "` `" . $colinfo->{'Field'} . "` " . $colinfo->{'Type'} . " not null default '1990-01-01'; n");
- }
- }
- }
- localmysql [localhost]> explain dt;
- +-------+------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+------+------+-----+---------+-------+
- | a | date | YES | | NULL | |
- +-------+------+------+-----+---------+-------+
- 1 row in set (0.00 sec)
- localmysql [localhost]> explain tst
- -> ;
- +-------+----------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+----------+------+-----+---------+-------+
- | id | int(11) | YES | | NULL | |
- | atime | datetime | YES | | NULL | |
- +-------+----------+------+-----+---------+-------+
- 2 rows in set (0.00 sec)
- update `dt` set `a` = '1990-01-01' where `a` IS NULL;
- alter table `dt` change column `a` `a` date not null default '1990-01-01';
- update `tst` set `atime` = '1990-01-01' where `atime` IS NULL;
- alter table `tst` change column `atime` `atime` datetime not null default '1990-01-01';
- CREATE VIEW migration_mytable AS
- SELECT field1, field2,
- CASE field3
- WHEN '0000-00-00 00:00:00'
- THEN '1900-01-01 00:00:00'
- ELSE field3
- END CASE AS field3
- FROM mytable;
- INSERT INTO sqlserver.mytable SELECT * FROM mysql.migration_mytable;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement