batch udpate sql批量更新数据库中所有表的特定字段值
Posted On 2021年9月30日
如下脚本实现了， 批量更新某个数据库中所有列字段 为 “date”，“timestamp”，“year”，“datetime”， 并将这些字段的值 均设置为 今年。
思路：
- 首先循环遍历某个db数据库的所有表， 依次查找是否包含这些字段类型的字段。 通过 information_schema. COLUMNS 表进行查询
- 找到所有的columns后，再遍历进行update 操作。 因为数据库表数量非常大， 且一些表的的数据可能会非常大（取决于表的实际记录数）再我们在存储过程中一次性执行非常大的update 语句操作时，会报错。 所以我们改为通过 EXECUTE stmt 方式来进行每一次update操作。 而不是直接使用update 语句。
脚本例子如下：
DROP PROCEDURE
IF EXISTS updatedate;
DELIMITER $$
CREATE PROCEDURE updatedate ()
BEGIN
DECLARE
n INT DEFAULT 5 ; DECLARE
i INT DEFAULT 0 ; DECLARE
datecount INT DEFAULT 0 ; DECLARE
timeStampcount INT DEFAULT 0 ; DECLARE
yearcount INT DEFAULT 0 ; DECLARE
datetimecount INT DEFAULT 0 ; #declare sql_text text default '';
DECLARE
tableschema VARCHAR (64) DEFAULT '' ; DECLARE
tablename VARCHAR (64) DEFAULT '' ; DECLARE
columnname VARCHAR (64) DEFAULT '' ; DECLARE
datatype VARCHAR (64) DEFAULT '' ; SELECT
count(*) INTO n
FROM
information_schema. COLUMNS
WHERE
table_schema IN ('databaseName1','databaseName2')
AND data_type IN (
'date',
'timestamp',
'year',
'datetime'
) ;
SET i = 0 ;
WHILE i < n DO
SELECT
table_schema,
table_name,
column_name,
data_type INTO tableschema,
tablename,
columnname,
datatype
FROM
information_schema. COLUMNS
WHERE
table_schema IN ('databaseName1','databaseName2')
AND data_type IN (
'date',
'timestamp',
'year',
'datetime'
)
LIMIT i,
1 ;
IF (datatype = 'date') THEN
SET @sql_text = concat(
'update ',
tableschema,
'.',
tablename,
' set ',
columnname,
' = DATE_ADD(',
columnname,
', INTERVAL if(',
columnname,
' > "2021-01-01 00:00:00",0,(2021 - YEAR(',
columnname,
'))) YEAR) where ',columnname,' is not null and ',columnname,' < "2021-01-01 00:00:00" '
) ;
SET datecount = datecount + 1 ;
ELSEIF (datatype = 'timestamp') THEN
SET @sql_text = concat(
'update ',
tableschema,
'.',
tablename,
' set ',
columnname,
' = DATE_ADD(',
columnname,
', INTERVAL if(',
columnname,
' > "2021-01-01 00:00:00",0,(2021 - YEAR(',
columnname,
'))) YEAR) where ',columnname,' is not null and ',columnname,' < "2021-01-01 00:00:00" '
) ;
SET timeStampcount = timeStampcount + 1 ;
ELSEIF (datatype = 'datetime') THEN
SET @sql_text = concat(
'update ',
tableschema,
'.',
tablename,
' set ',
columnname,
' = DATE_ADD(',
columnname,
', INTERVAL if(',
columnname,
' > "2021-01-01 00:00:00",0,(2021 - YEAR(',
columnname,
'))) YEAR) where ',columnname,' is not null and ',columnname,' < "2021-01-01 00:00:00" '
) ;
SET datetimecount = datetimecount + 1 ;
ELSEIF (datatype = 'year') THEN
SET @sql_text = concat(
'update ',
tableschema,
'.',
tablename,
' set ',
columnname,
' = if(',
columnname,
'>= 2021 , ',
columnname,
', 2021) where ',columnname,' is not null and ',columnname,' < 2021 '
) ;
SET yearcount = yearcount + 1 ;
END
IF ;
PREPARE stmt FROM @sql_text ;
EXECUTE stmt ;
SET i = i + 1 ;
END
WHILE ;
SELECT
n,
datecount,
yearcount,
timeStampcount,
datetimecount;
END$$
DELIMITER ;
CALL updatedate;
此篇文章已被阅读651 次