mysql 储存过程将数据库int整型时间戳批量转换成datetime类型
时间: 2023-05-30 08:01:44 浏览: 121
MySql数据类型
可以使用以下存储过程将数据库int类型的时间戳批量转换成datetime类型:
DELIMITER $$
CREATE PROCEDURE `ConvertTimestampsToDatetime`()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE timestamp_val INT;
DECLARE cursor_timestamps CURSOR FOR SELECT your_timestamp_field FROM your_table_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cursor_timestamps;
read_loop: LOOP
FETCH cursor_timestamps INTO timestamp_val;
IF done THEN
LEAVE read_loop;
END IF;
UPDATE your_table_name SET your_datetime_field = FROM_UNIXTIME(timestamp_val) WHERE your_timestamp_field = timestamp_val;
END LOOP;
CLOSE cursor_timestamps;
END$$
DELIMITER ;
在上面的存储过程中,将your_table_name替换为实际的表名,将your_timestamp_field替换为实际的时间戳字段名,将your_datetime_field替换为实际的datetime类型字段名。该存储过程将循环遍历所有的时间戳值,使用FROM_UNIXTIME()函数将其转换成datetime类型,并更新到对应的字段中。
阅读全文