我在MySQL中有很多表,其中dateTime列中包含零日期0000-00-00 00:00:00
使用某种管理设置,是否可以禁用零日期并将所有零替换为静态值,例如1-1-1900
?
编辑:
我正在进行数据库迁移,涉及将100多个MySQL表迁移到SQL Server。
通过设置数据库模式,我是否可以避免在每个表上手动执行脚本?
我在MySQL中有很多表,其中dateTime列中包含零日期0000-00-00 00:00:00
使用某种管理设置,是否可以禁用零日期并将所有零替换为静态值,例如1-1-1900
?
编辑:
我正在进行数据库迁移,涉及将100多个MySQL表迁移到SQL Server。
通过设置数据库模式,我是否可以避免在每个表上手动执行脚本?
要更改现有值,您可以使用以下查询:
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 ;
这是一个老问题,但我遇到了类似的问题,我试图将0000-00-00设置为NULL。
我尝试查询如下:
UPDATE tablename SET date_column = NULL WHERE date_column = '0000-00-00';
但是出现以下错误:
列'date_column'的值'0000-00-00'不是有效的日期值,位于第1行
事实证明,以下查询没有'',可以正常工作!
UPDATE tablename SET date_column = NULL WHERE date_column = 0000-00-00;
您可以通过运行该查询更改现有的值。
update your_table
set date_column = '1900-01-01'
where date_column = '0000-00-00'
您可以像这样更改表的定义为特定的默认值或 null
ALTER TABLE your_table
CHANGE date_column date_column date NOT NULL DEFAULT '1900-01-01'
INFORMATION_SCHEMA
,可能是COLUMNS
,并构建一个查询来获取你需要影响的表,例如:-
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");
从 @string 准备 stmt 语句; 执行 stmt;
这并不太难写出来。
选项二——另一个例子,虽然显然更加低技术,但可能同样有效。在执行导出之前,进行 mysqldump
后,您可以在文件中执行简单的搜索替换。Vim 或任何其他文本编辑器都可以很好地完成此操作,并允许您将 0000-00-00 00:00:00 替换为 1-1-1900。因为您几乎肯定找不到不想要被替换的情况,所以这可能是您最简单的选择。只是给您提供一个思路!
select
concat('UPDATE ',TABLE_NAME,' SET ',COLUMN_NAME,'=NULL WHERE ',COLUMN_NAME,'=0;')
from information_schema.COLUMNS
where TABLE_SCHEMA = 'DATABASE_NAME' and DATA_TYPE in ('datetime', 'date', 'time');
只需将DATABASE_NAME
替换为您的数据库名称,并执行所有更新。
将您的表格更改为
ALTER TABLE `test_table`
CHANGE COLUMN `created_dt` `created_dt` date NOT NULL DEFAULT '1900-01-01';
但在修改表之前,您需要像Juergen D所说的那样更新现有值。
update test_table
set created_dt= '1900-01-01'
where created_dt= '0000-00-00'
前缀: 在数据仓库中,您可能想要检查ETL的概念,有一些工具可以为您完成简单转换,甚至像Kettle/Pentaho这样的开源工具也可以。
但是当您使用任何能够组合SQL查询的编程语言时,这个任务就变得很容易了。 我已经用Perl做了一个例子,但PHP或Java也可以胜任此工作:
#!/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';
这个列表可以被查看并作为语句执行。
希望能有所帮助!
由于这是为迁移而进行的操作,我建议您只需在视图中包装表格,以便在导出数据时进行转换。我曾经使用下面的概念将数据从MySQL迁移到具有相同问题的PostgreSQL。
每个表格应该由类似以下内容的代理来处理:
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;
如果你需要处理大量表格,你应该能够编写一个脚本从目录中生成这个。
然后,你应该能够将数据导入到你的 SqlServer 表中(使用像 this 这样的桥接器),并简单地运行查询,如下所示;
INSERT INTO sqlserver.mytable SELECT * FROM mysql.migration_mytable;
您可以通过筛选日期等于0的数据并定义列的默认值来更新
表格。