当使用默认值NULL时，MySQL浮点值全部变为NULL [英] MySQL float values all become NULL when default NULL is used

问题描述

我创建下面的表，并将FLOAT的默认值设置为NULL，因为我想要缺少浮点值在表中存储为NULL。但问题是我在表中设置默认为NULL后，所有customerReviewAverage值变为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产品
终止终止'，'
终止'''
终止'\\\
 '
（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 
  
后来我发现
 （sku，name，@description，regularPrice，@customerReviewAverage）
  
应修改为
 （sku，name，@description，regularPrice，customerReviewAverage）
  
 
 
解决方案
尝试此
 （sku，name，@description，regularPrice，@customerReviewAverage）
 SET 
 description = IF（@description =''，NULL，@ description），
 customerReviewAverage = IF（@customerReviewAverage =''，NULL，@ customerReviewAverage）; 
  
您的缺少值是CSV中的空文本。 
 
 
 
由于您的 mysql 版本没有运行此操作，您可以单独进行更新：
  UPDATE产品SET customerReviewAverage = NULL WHERE customerReviewAverage = 0 
  
 
I create the following table and set default value of FLOAT as NULL because I want missing float value to be stored as NULL in the table. But the problem is after I set default as NULL in the table, all customerReviewAverage values become NULL. 
The following are the code 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
Later I found that 
(sku, name, @description, regularPrice, @customerReviewAverage)
should be modified as
(sku, name, @description, regularPrice, customerReviewAverage)

解决方案
Try this
(sku, name, @description, regularPrice, @customerReviewAverage)
SET 
description = IF(@description='',NULL,@description),
customerReviewAverage = IF(@customerReviewAverage='',NULL,@customerReviewAverage);
Your "missing" values are an empty text in the CSV. 

Since your mysql version isn't running this ok, you can do a separate update:
UPDATE Products SET customerReviewAverage = NULL WHERE customerReviewAverage = 0


                        
这篇关于当使用默认值NULL时，MySQL浮点值全部变为NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！