gpt4 book ai didi

mysql - 我在插入选择时得到的日期值不正确

转载 作者:行者123 更新时间:2023-11-29 02:12:44 26 4
gpt4 key购买 nike

我想做一个插入选择,但在 phpmyadmin 中我不断收到错误 #1292 - 不正确的日期值:'' for column 'Date' at row 1现在 select 可以自己正常工作而不会出错。我只有在添加 INSERT 语句时才会收到错误,例如这行得通

SELECT
`SalesInvoice_id`,
`Date`,
IFNULL(`valueOriginal`+`VATOriginal`,0) as `Amount`
FROM
(SELECT
`salesinvoice`.`SalesInvoice_id`,
`DatePaid` AS `Date`,
ROUND(SUM((`Unit_Cost`*`Quantity`)),2) as `valueOriginal`,
IF(`salesinvoice`.`VAT`=2,ROUND(SUM(((`Unit_Cost`)*`Quantity`)/100*`salesinvoice_products`.`VAT`),2),0) as `VATOriginal`,
`InvoiceType`
FROM `salesinvoice`
LEFT JOIN `salesinvoice_products` ON `salesinvoice`.`SalesInvoice_id` = `salesinvoice_products`.`SalesInvoice_id`
GROUP BY `salesinvoice`.`SalesInvoice_id`
) a
WHERE `InvoiceType`!='3' && `Date`!=''

但是当我这样做的时候

INSERT INTO `salesinvoice_payments` (`SalesInvoice_id`,`Date`,`Amount`)
SELECT
`SalesInvoice_id`,
`Date`,
IFNULL(`valueOriginal`+`VATOriginal`,0) as `Amount`
FROM
(SELECT
`salesinvoice`.`SalesInvoice_id`,
`DatePaid` AS `Date`,
ROUND(SUM((`Unit_Cost`*`Quantity`)),2) as `valueOriginal`,
IF(`salesinvoice`.`VAT`=2,ROUND(SUM(((`Unit_Cost`)*`Quantity`)/100*`salesinvoice_products`.`VAT`),2),0) as `VATOriginal`,
`InvoiceType`
FROM `salesinvoice`
LEFT JOIN `salesinvoice_products` ON `salesinvoice`.`SalesInvoice_id` = `salesinvoice_products`.`SalesInvoice_id`
GROUP BY `salesinvoice`.`SalesInvoice_id`
) a
WHERE `InvoiceType`!='3' && `Date`!=''

我得到了错误。好的,这是我的架构

DROP TABLE IF EXISTS salesinvoice_payments;
DROP TABLE IF EXISTS salesinvoice;
DROP TABLE IF EXISTS salesinvoice_products;

CREATE TABLE salesinvoice_payments
(`sipID` int(11) NOT NULL AUTO_INCREMENT,
`salesinvoice_id` int,
`Date` date,
`Amount` int,
PRIMARY KEY (`sipID`))
;

CREATE TABLE salesinvoice (
`SalesInvoice_id` int(11) NOT NULL AUTO_INCREMENT,
`VAT` tinyint(1) DEFAULT NULL,
`DatePaid` date DEFAULT NULL,
`InvoiceType` tinyint(1) DEFAULT 1,
PRIMARY KEY (`SalesInvoice_id`)
);

CREATE TABLE salesinvoice_products (
product_ID int(11) NOT NULL AUTO_INCREMENT,
SalesInvoice_id int(11) DEFAULT NULL,
Description varchar(255) DEFAULT NULL,
Unit_Cost decimal(19, 2) DEFAULT NULL,
Quantity int(11) DEFAULT NULL,
VAT decimal(10, 1) DEFAULT NULL,
PRIMARY KEY (product_ID)
);

INSERT INTO `salesinvoice`
(`SalesInvoice_id`,`VAT`, `DatePaid`, `InvoiceType`)
VALUES
(1,2,'2017-11-07',1),
(2,2,'2017-11-07',1),
(3,2,'2017-11-07',1),
(4,2,'2017-11-07',1),
(5,2,'2017-11-07',1),
(6,2,'2017-11-07',1)
;

INSERT INTO `salesinvoice_products`
(`SalesInvoice_id`, `Description`, `Unit_Cost`,`Quantity`,`VAT`)
VALUES
(3,'Sausage',1.50,5,21),
(3,'Cabbage',2.50,15,21),
(4,'Herring',2.50,15,21),
(4,'Red',2.50,15,21),
(3,'Dongle',2.50,15,21),
(2,'Thingy',2.50,15,21),
(2,'Spaceship',2.50,15,21),
(1,'Car',2.50,15,21),
(1,'Alien',2.50,15,21)
;

INSERT INTO `salesinvoice_payments` (`SalesInvoice_id`,`Date`,`Amount`)
SELECT
`SalesInvoice_id`,
`Date`,
IFNULL(`valueOriginal`+`VATOriginal`,0) as `Amount`
FROM
(SELECT
`salesinvoice`.`SalesInvoice_id`,
`DatePaid` AS `Date`,
ROUND(SUM((`Unit_Cost`*`Quantity`)),2) as `valueOriginal`,
IF(`salesinvoice`.`VAT`=2,ROUND(SUM(((`Unit_Cost`)*`Quantity`)/100*`salesinvoice_products`.`VAT`),2),0) as `VATOriginal`,
`InvoiceType`
FROM `salesinvoice`
LEFT JOIN `salesinvoice_products` ON `salesinvoice`.`SalesInvoice_id` = `salesinvoice_products`.`SalesInvoice_id`
GROUP BY `salesinvoice`.`SalesInvoice_id`
) a
WHERE `InvoiceType`!='3' && `Date`!=''

好的,这是我第一次在堆栈溢出上发帖,我将展示我的 sqlfiddle。选择插入在 sqlfiddle 中工作正常,但在 phpmyadmin 中不行。但是在 phpmyadmin 中,我收到错误 #1292 - 日期值不正确:第 1 行的“日期”列为“”。 SQLFIDDLE

由于某种原因,日期未正确发送。

最佳答案

当您选择时,您会收到相同的警告。

mysql> SELECT ... WHERE `InvoiceType`!='3' && `Date`!='';
+-----------------+------------+--------+
| SalesInvoice_id | Date | Amount |
+-----------------+------------+--------+
| 1 | 2017-11-07 | 90.75 |
| 2 | 2017-11-07 | 90.75 |
| 3 | 2017-11-07 | 99.83 |
| 4 | 2017-11-07 | 90.75 |
| 5 | 2017-11-07 | 0.00 |
| 6 | 2017-11-07 | 0.00 |
+-----------------+------------+--------+
6 rows in set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1292 | Incorrect date value: '' for column 'Date' at row 1 |
+---------+------+-----------------------------------------------------+

而当我们离开那个条件时没有警告。

mysql> SELECT ... WHERE `InvoiceType`!='3';
+-----------------+------------+--------+
| SalesInvoice_id | Date | Amount |
+-----------------+------------+--------+
| 1 | 2017-11-07 | 90.75 |
| 2 | 2017-11-07 | 90.75 |
| 3 | 2017-11-07 | 99.83 |
| 4 | 2017-11-07 | 90.75 |
| 5 | 2017-11-07 | 0.00 |
| 6 | 2017-11-07 | 0.00 |
+-----------------+------------+--------+
6 rows in set (0.01 sec)

所以它提示的是日期列与 '' 的比较,而不是向表中插入值。

将 MySQL 日期与 '' 进行比较是没有意义的。 DATEDATETIME 类型的列不能为空字符串。无效的日期被转换为 0000-00-00,或者如果你使用严格模式,它们是一个错误。您永远无法获得 '' 的日期列。

可以获取为NULL 的日期列。那可能是您要测试的内容。 NULL 和空字符串 '' 在标准 SQL 和 MySQL 中是不同的。

我猜你有 Oracle 经验,其中 NULL 和空字符串在许多上下文中被视为相同(Oracle 的这种行为不是标准 SQL)。

我建议您在要过滤掉 NULL 时使用 IS NOT NULL

mysql> SELECT ... WHERE `InvoiceType`!='3' AND `Date` IS NOT NULL;
+-----------------+------------+--------+
| SalesInvoice_id | Date | Amount |
+-----------------+------------+--------+
| 1 | 2017-11-07 | 90.75 |
| 2 | 2017-11-07 | 90.75 |
| 3 | 2017-11-07 | 99.83 |
| 4 | 2017-11-07 | 90.75 |
| 5 | 2017-11-07 | 0.00 |
| 6 | 2017-11-07 | 0.00 |
+-----------------+------------+--------+
6 rows in set (0.01 sec)

关于mysql - 我在插入选择时得到的日期值不正确,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47416079/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com