我在MySQL中有很多表，其中dateTime列中包含零日期0000-00-00 00:00:00
使用某种管理设置，是否可以禁用零日期并将所有零替换为静态值，例如1-1-1900？
编辑：
我正在进行数据库迁移，涉及将100多个MySQL表迁移到SQL Server。
通过设置数据库模式，我是否可以避免在每个表上手动执行脚本？
从MySQL数据库中跨越所有表删除所有零日期
5
- Abhijeet
2
我不熟悉MySQL中的数据库模式。你有相关的参考资料吗？你绝对不需要手动进行更改！ - smcjones
为什么还没有选择正确的答案？ - Ashok Raj
9个回答
8
要更改现有值，您可以使用以下查询：
UPDATE tablename SET date_column = '1900-01-01' WHERE date_column = '0000-00-00';
如果您想自动化UPDATE查询，可以使用预处理语句：
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 ;
请查看这个例子here，它与IT技术有关。
- fthiella
6
这是一个老问题，但我遇到了类似的问题，我试图将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;
- 0cnLaroche
3
这也适用于启用 SQL-Mode NO_ZERO_IN_DATE 的“新”的 5.7 函数 - 非常感谢，这节省了我的一天！ - sneaky
我遇到了同样的问题，这个语句解决了它：UPDATE tablename SET date_column = '1900-01-01' WHERE date_column = 0000-00-00; - karthzDIGI
您先生，真是救了我一命！谢谢您！我一直在撞墙 :D - jAC
2
您可以通过运行该查询更改现有的值。
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'
- juergen d
1
谢谢，我应该更具体一些，请查看我的编辑。 - Abhijeet
2
你有两个选择。
选项一 - 使用你选择的编程语言（甚至可以使用存储过程）：
- 循环遍历你的
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>'
- 存储结果，然后遍历它们。每次循环，创建一个新的查询。在MySQL中，这将是一个带有预处理语句的存储过程，即：
-
@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。因为您几乎肯定找不到不想要被替换的情况，所以这可能是您最简单的选择。只是给您提供一个思路！
- smcjones
1
在我看来，你可以用最简单的方式生成所有更新：
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替换为您的数据库名称，并执行所有更新。
- Marek
0
将您的表格更改为
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'
- Vignesh Kumar A
3
在添加约束之前，我必须更新所有行，对吗？ - Abhijeet
那不是一个约束定义。 - juergen d
@VigneshKumar：这并不是必须要做的。 - juergen d
0
前缀: 在数据仓库中，您可能想要检查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';
这个列表可以被查看并作为语句执行。
希望能有所帮助！
- flaschenpost
0
由于这是为迁移而进行的操作，我建议您只需在视图中包装表格，以便在导出数据时进行转换。我曾经使用下面的概念将数据从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;
- Soren
0
您可以通过筛选日期等于0的数据并定义列的默认值来更新表格。
- Florentin Le Moal
1
2你能否提供一个快速的例子呢？这将真正改善答案！ - msturdy
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接