Mysql错误：1292 不正确的日期时间值显示不存在的日期

Question

Mysql错误：1292 不正确的日期时间值显示不存在的日期

3

我在使用这个存储过程时遇到了错误：

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `populateTimeTable`(IN table_name VARCHAR(45), IN start_date DATE, IN end_date DATE)
BEGIN

DECLARE full_date DATE;

SET @query_create = CONCAT('CREATE TABLE IF NOT EXISTS ' ,table_name, '(
    idDate INT(8) UNSIGNED NOT NULL,
    date DATE NOT NULL,
    year SMALLINT(4) UNSIGNED NOT NULL,
    quarter TINYINT(1) UNSIGNED NOT NULL,
    month tinyint(2) unsigned not null,
    month_name ENUM(''January'',''February'',''March'',''April'',''May'',''June'',''July'',''August'',''September'',''October'',''November'',''December'') NOT NULL,
    month_name_spanish ENUM(''Enero'',''Febrero'',''Marzo'',''Abril'',''Mayo'',''Junio'',''Julio'',''Agosto'',''Septiembre'',''Octubre'',''Noviembre'',''Diciembre'') NOT NULL,
    week TINYINT(2) UNSIGNED NOT NULL,
    day TINYINT(2) UNSIGNED NOT NULL,
    weekday TINYINT(1) UNSIGNED NOT NULL,
    weekday_name ENUM(''Monday'',''Tuesday'',''Wednesday'',''Thursday'',''Friday'',''Saturday'',''Sunday'') NOT NULL,
    weekday_name_spanish ENUM(''Lunes'',''Martes'',''Miércoles'',''Jueves'',''Viernes'',''Sábado'',''Domingo'') NOT NULL,
    PRIMARY KEY(idDate)
)');
PREPARE stmt1 FROM @query_create;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

SET @query_delete = CONCAT('DELETE FROM ' ,table_name);
PREPARE stmt2 FROM @query_delete;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

SET full_date = start_date;

WHILE full_date <= end_date
DO
    SET @query_insert = CONCAT('INSERT INTO ' ,table_name, '(
        idDate,
        date,
        year,
        quarter,
        month,
        month_name,
        month_name_spanish,
        week,
        day,
        weekday,
        weekday_name,
        weekday_name_spanish
    ) VALUES ( 
        DATE_FORMAT(' ,full_date, ', "%Y%m%d"),'
        ,full_date, ',
        YEAR(' ,full_date, '),
        QUARTER(' ,full_date, '),
        MONTH(' ,full_date, '),
        MONTHNAME(' ,full_date, '),
        (SELECT CASE MONTH(' ,full_date, ')
            WHEN 1 THEN ''Enero''
            WHEN 2 THEN ''Febrero''
            WHEN 3 THEN ''Marzo''
            WHEN 4 THEN ''Abril''
            WHEN 5 THEN ''Mayo''
            WHEN 6 THEN ''Junio''
            WHEN 7 THEN ''Julio''
            WHEN 8 THEN ''Agosto''
            WHEN 9 THEN ''Septiembre''
            WHEN 10 THEN ''Octubre''
            WHEN 11 THEN ''Noviembre''
            WHEN 12 THEN ''Diciembre''
        END),
        WEEK(' ,full_date, ', 5),
        DAY(' ,full_date, '),
        WEEKDAY(' ,full_date, ') + 1,
        DAYNAME(' ,full_date, '),
        (SELECT CASE DAYOFWEEK(' ,full_date, ')
            WHEN 1 THEN ''Domingo''
            WHEN 2 THEN ''Lunes''
            WHEN 3 THEN ''Martes''
            WHEN 4 THEN ''Miércoles''
            WHEN 5 THEN ''Jueves''
            WHEN 6 THEN ''Viernes''
            WHEN 7 THEN ''Sábado''
        END)
    )');
    PREPARE stmt3 FROM @query_insert;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;

    SET full_date = DATE_ADD(full_date, INTERVAL 1 DAY);
END WHILE;
END

如果我执行：

CALL populateTimeTable('dim_time','1900-04-01','1901-01-01')

我得到：

Error Code: 1292 Incorrect datetime value: '1895'

去掉所有的预处理语句并使用固定的表名，代码可以运行，因此问题出在预处理语句stmt3上。

如果我改变开始日期，会得到相同的错误但是值不同。

- vjsp90

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

问题在于你没有在创建的查询字符串中给full_date加上引号。所以，你得到的结果可能是这样的：

VALUES ( 
        DATE_FORMAT( 1900-04-01, "%Y%m%d"),'
        1900-04-01,
        . . .

这与以下内容不同：

VALUES ( 
        DATE_FORMAT('1900-04-01', "%Y%m%d"),'
        1900-04-01,
        . . .

你的版本进行了一次算术计算（1900-04-01=1895），因此它变成了：

VALUES ( 
        DATE_FORMAT( 1895, "%Y%m%d"),'
        1895,
        . . .

顺便说一下，如果你查看变量替换后的字符串，这个问题可能会很明显。无论何时使用动态SQL，都应该始终查看所生成的查询字符串。
你可以通过在语句中添加单引号来修复此问题。或者，更好的做法是参数化语句并使用EXECUTE . . . USING（请参见此处）。
编辑：
您可以以以下形式编写查询语句：

insert into table_name(. . . )
    select DATE_FORMAT(full_date, '%Y%m%d), 
           full_date,
           YEAR(full_date),
           . . .
    from (select ? as full_date, . . .) t;

每个参数都在子查询中。然后可以在外部查询中使用这些值。