mysql - 将数据库中的所有 DATETIME 值动态转换为 MySQL 中的 UTC

Question

目前，我们应用程序的数据库以 GMT-8 格式存储所有 DATETIME 值，这通常非常糟糕。我们正在尝试将应用程序和所有数据转换为使用 UTC。

我正在尝试编写一个一次性存储过程，它将查询我的数据库架构，查找类型为 DATETIME 或 TIMESTAMP 的所有列，并自动更新列使用 CONVERT_TZ() 函数转换值。

使用我迄今为止发现的有关游标、动态语句准备和错误处理的所有信息，我得出了以下结论:

DELIMITER $$

CREATE PROCEDURE `sp_convertDBtoUTC`()
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE done INT DEFAULT 0;
    DECLARE tname VARCHAR(64);
    DECLARE cname VARCHAR(64);

    DECLARE dt_columns CURSOR FOR 
        SELECT table_name, column_name 
        FROM information_schema.columns
        WHERE table_schema = 'my_schema' 
            AND data_type IN ('datetime', 'timestamp')
        ORDER BY table_name, column_name;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

    START TRANSACTION;

    OPEN dt_columns;

    table_loop:
    LOOP
        FETCH dt_columns INTO tname, cname;

        IF done = 1 THEN
            LEAVE table_loop;
        END IF;

        SET @stmt = CONCAT('UPDATE ',tname,' SET ',cname, ' = CONVERT_TZ(',cname, ', \'America/Los_Angeles\', \'UTC\')');
        PREPARE stmt FROM @stmt;
        EXECUTE stmt;

        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE dt_columns;

    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;

END$$

这似乎运行没有错误，但什么也没做。如果我在其中添加一个 SELECT @stmt 语句，我会得到多个具有正确 SQL 的结果集，例如UPDATE tz_test SET dts = CONVERT_TZ(dts, 'America/Los_Angeles', 'UTC')。如果我手动运行此语句，该表会按预期更新。

但是，当运行存储过程来更新整个数据库时，表中的数据实际上并没有得到更新，我不明白为什么。

我正在使用以下内容作为测试:

CREATE TABLE tz_test (id INT auto_increment, dts DATETIME, PRIMARY KEY (id));
INSERT INTO tz_test (dts) VALUES (NOW());

SELECT * FROM tz_test;
CALL sp_convertDBtoUTC();
SELECT * FROM tz_test;

我本来希望第二个结果集包含修改后的日期时间值，但两个结果集是相同的。这是为什么？

Answer 1

当尝试通过 MySQL Workbench 运行这些查询时，由于安全模式(没有引用主键的情况下无法进行大规模更新/删除)，它们实际上无提示地失败了。

一旦我将其关闭并重新连接，整个过程就完美无缺。