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

我在此存储过程中遇到了错误：

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上.

如果更改开始日期,则会出现相同的错误,但值不同.

解决方法:

问题是您在创建的查询字符串中在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. . .使用(请参阅here).

编辑：

您可以使用以下形式编写查询：

insert into table_name(. . . )

select DATE_FORMAT(full_date, '%Y%m%d),

full_date,

YEAR(full_date),

. . .

from (select ? as full_date, . . .) t;

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

标签：prepared-statement,mysql,mysql-error-1292

来源： https://codeday.me/bug/20191119/2039369.html