mysql - 如何在 SQL 中使用 NULL 存储缺失的 FLOAT 值

Question

在另一个问题中，我问了为什么非空值会变成 NULL。但是在这个问题中，我询问了如何使空值变为 NULL 但不为零。

我发现缺少的浮点值总是表示为 0，而不是 NULL。我该如何改变它？

以下是我创建表格和加载数据的代码。

CREATE TABLE Products(sku INTEGER, name VARCHAR(255), description TEXT,
                          regularPrice FLOAT,
                          customerReviewAverage FLOAT default NULL );
LOAD DATA LOCAL INFILE 'product.csv'
    INTO TABLE Products
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    (sku, name, @description, regularPrice, customerReviewAverage)
    SET description = IF(@description='',NULL,@description);

这是 product.csv 中的数据示例。

19658847,Glanzlichter - CD,,12.99,5.0
19658856,Glanzlichter - CD,,6.99,
19658865,Glanzlichter - CD,,8.99,
1965886,Beach Boys '69 - CASSETTE,,6.99,4.5

Answer 1

问题在于 MySQL 如何解释空字段 与缺失字段。 From the docs for LOAD DATA INFILE ...

If an input line has too few fields, the table columns for which input fields are missing are set to their default values.

An empty field value is interpreted different from a missing field. For string types, the column is set to the empty string. For numeric types, the column is set to 0.

在这种情况下，MySQL 似乎认为它是空的。您可以从 show warnings 中看到这一点。

mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'customerReviewAverage' at row 2 |
| Warning | 1265 | Data truncated for column 'customerReviewAverage' at row 3 |
+---------+------+------------------------------------------------------------+
2 rows in set (0.00 sec)

而如果我们删除尾随逗号，那么该字段将丢失...

19658847,Glanzlichter - CD,,12.99,5.0
19658856,Glanzlichter - CD,,6.99
19658865,Glanzlichter - CD,,8.99
1965886,Beach Boys '69 - CASSETTE,,6.99,4.5

然后数据设置为空。

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1261 | Row 2 doesn't contain data for all columns |
| Warning | 1261 | Row 3 doesn't contain data for all columns |
+---------+------+--------------------------------------------+

mysql> select * from products;   
+----------+---------------------------+-------------+--------------+-----------------------+
| sku      | name                      | description | regularPrice | customerReviewAverage |
+----------+---------------------------+-------------+--------------+-----------------------+
| 19658847 | Glanzlichter - CD         | NULL        |        12.99 |                     5 |
| 19658856 | Glanzlichter - CD         | NULL        |         6.99 |                  NULL |
| 19658865 | Glanzlichter - CD         | NULL        |         8.99 |                  NULL |
|  1965886 | Beach Boys '69 - CASSETTE | NULL        |         6.99 |                   4.5 |
+----------+---------------------------+-------------+--------------+-----------------------+
4 rows in set (0.00 sec)

---

对 @customerReviewAverage 和 @description 做同样的事情在 MySQL 5.7 中对我有用。

LOAD DATA
  LOCAL INFILE 'product.csv'
  INTO TABLE Products
  FIELDS TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  (sku, name, @description, regularPrice, @customerReviewAverage)       
  SET description           = IF(@description='',NULL,@description),
      customerReviewAverage = IF(@customerReviewAverage='',NULL,@customerReviewAverage);

mysql> select * from products;
+----------+---------------------------+-------------+--------------+-----------------------+
| sku      | name                      | description | regularPrice | customerReviewAverage |
+----------+---------------------------+-------------+--------------+-----------------------+
| 19658847 | Glanzlichter - CD         | NULL        |        12.99 |                     5 |
| 19658856 | Glanzlichter - CD         | NULL        |         6.99 |                  NULL |
| 19658865 | Glanzlichter - CD         | NULL        |         8.99 |                  NULL |
|  1965886 | Beach Boys '69 - CASSETTE | NULL        |         6.99 |                   4.5 |
+----------+---------------------------+-------------+--------------+-----------------------+
4 rows in set (0.00 sec)