如何在SQL中存储缺少的FLOAT值和NULL [英] How to store missing FLOAT values with NULL in SQL

问题描述

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

我发现缺少的浮点值总是表示为0，但不是NULL 。如何更改？

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

  CREATE TABLE产品（sku INTEGER，name VARCHAR（255），description TEXT，
 regularPrice FLOAT，
 customerReviewAverage FLOAT default NULL）; 
 LOAD DATA LOCAL INFILE'product.csv'
 INTO TABLE产品
终止终止'，'
终止'''
终止'\\\
 '
（sku，name，@description，regularPrice，customerReviewAverage）
 SET description = IF（@description =''，NULL，@ description）; 
  / pre> 
 
 
这是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 
  
 
 
解决方案
问题是MySQL如何解释  字段。从LOAD DATA INFILE的文档 ... 
 
 
如果输入行太少字段，缺少输入字段的表列设置为其默认值。
 
 
 
空字段值与缺少的字段不同。对于字符串类型，列设置为空字符串。 
 
 
在这种情况下，似乎MySQL认为它是空的
 em>。您可以从显示警告看到这个。
  mysql>显示警告; 
 + --------- + ------ + ---------------------------- -------------------------------- + 
 |级别|代码|消息| 
 + --------- + ------ + ---------------------------- -------------------------------- + 
 |警告| 1265 |在第2行|列'customerReviewAverage'截断的数据
 |警告| 1265 |在第3行|的customerReviewAverage列截断的数据
 + --------- + ------ + ---------------------------- -------------------------------- + 
集合中的2行（0.00秒）
  
 
 
 
但是，如果我们删除尾部逗号，因此字段会丢失 ... 
  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 
  
然后将数据设置为null。
  mysql>显示警告; 
 + --------- + ------ + ---------------------------- ---------------- + 
 |级别|代码|消息| 
 + --------- + ------ + ---------------------------- ---------------- + 
 |警告| 1261 |第2行不包含所有列的数据
 |警告| 1261 |第3行不包含所有列的数据
 + --------- + ------ + ---------------------------- ---------------- + 
 
 mysql>选择*从产品; 
 + ---------- + --------------------------- + ------ ------- + -------------- + ----------------------- + 
 | sku |名称|描述| regularPrice | customerReviewAverage | 
 + ---------- + --------------------------- + ------ ------- + -------------- + ----------------------- + 
 | 19658847 | Glanzlichter  -  CD | NULL | 12.99 | 5 | 
 | 19658856 | Glanzlichter  -  CD | NULL | 6.99 | NULL | 
 | 19658865 | Glanzlichter  -  CD | NULL | 8.99 | NULL | 
 | 1965886 |海滩男孩'69  -  CASSETTE | NULL | 6.99 | 4.5 | 
 + ---------- + --------------------------- + ------ ------- + -------------- + ----------------------- + 
集合中的4行（0.00秒）
  
 
 
 
 
 
 
对于 @customerReviewAverage 和 @description 一样，在MySQL 5.7中为我工作。
  LOAD DATA 
 LOCAL INFILE'product.csv'
 INTO TABLE产品
 FIELDS TERMINATED BY'，'
'''
''''
''''
''''''
' 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 |海滩男孩'69  -  CASSETTE | NULL | 6.99 | 4.5 | 
 + ---------- + --------------------------- + ------ ------- + -------------- + ----------------------- + 
集合中的4行（0.00秒）
  
 
In the other question, I asked about why non-empty values become NULL. But in this question, I asked about how I could make empty values become NULL but not zero.

I found that missing float values are always represented as  0, but not NULL. How could I change that?

The following are the codes with which I created the table and loaded data.
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);
This is a sample of data in 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

解决方案
The issue is how MySQL interprets an empty field vs a missing field.  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. 
In this case it seems MySQL considers it to be empty.  You can see this from 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)
Whereas if we remove the trailing commas so the field is missing...
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
Then the data is set to null.
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)




Doing the same thing for @customerReviewAverage as for @description worked for me in 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)


                        
这篇关于如何在SQL中存储缺少的FLOAT值和NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！