batch udpate sql批量更新数据库中所有表的特定字段值

如下脚本实现了, 批量更新某个数据库中所有列字段 为 “date”,“timestamp”,“year”,“datetime”, 并将这些字段的值 均设置为 今年。

思路:

  1. 首先循环遍历某个db数据库的所有表, 依次查找是否包含这些字段类型的字段。 通过 information_schema. COLUMNS 表进行查询
  2. 找到所有的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;

此篇文章已被阅读47 次

Add a Comment

邮箱地址不会被公开。 必填项已用*标注