Mysql

MySQL 在數字列中載入 NULL

  • June 25, 2015

MySQL 5.6.23，InnoDB

我正在使用LOAD DATA INFILEcommnd 從字元分隔的文本文件中載入表格，並且我希望每個帶有 a 的欄位（此設置\N中的NULL字元）都將 aNULL放在表格中。一些數字類型有這種行為，而另一些則放置一個0. 我正在使用FIELDS TERMINATED BY並且某些列確實可以正確獲取NULL值，因此它不是固定行格式問題。

這些是我在測試時觀察到的類型：

  • INT插入NULLs
  • DECIMAL(x,0)插入NULLs
  • DECIMAL(x,y)插入0.0s
  • FLOAT插入0s
  • DOUBLE(x,y)插入0.0s
  • DOUBLE插入0s

所有有問題的列都用 定義DEFAULT NULL。我知道各種函式可以將這些0s 轉換為NULLs。問題是是否存在可以處理小數精度並且還會NULL在載入時插入 s 的數據類型。

此外，我看到一大堆與誤解字元串、空字元串和空值之間的區別有關的問題。（範例 範例 ）這不是問題，因為當我將其重新定義為 DECIMAL(x,0) 時，s 存在並正確載入到同一列中，然後在定義為 DECIMAL(x,3) 時不正確。NULL

非常簡短的回答：沒有創建新的數據類型來適應您。

當我們討論這個主題時

讓我們嘗試簡單的 SQL

USE test
DROP TABLE IF EXISTS numtest;
CREATE TABLE numtest
(
 id int not null auto_increment,
 xx decimal(10,3) default null,
 primary key (id)
);
INSERT INTO numtest (id) values (0),(0),(0),(0),(0);
SELECT * FROM numtest;

這行得通嗎？？？

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS numtest;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE numtest
   -> (
   ->   id int not null auto_increment,
   ->   xx decimal(10,3) default null,
   ->   primary key (id)
   -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO numtest (id) values (0),(0),(0),(0),(0);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM numtest;
+----+------+
| id | xx   |
+----+------+
|  1 | NULL |
|  2 | NULL |
|  3 | NULL |
|  4 | NULL |
|  5 | NULL |
+----+------+
5 rows in set (0.00 sec)

mysql>

好的。它適用於 SQL。你問的是LOAD DATA INFILE

您提出了我回答的文章：MySQL 在十進製欄位中將“”插入為 0。如何阻止它？

讓我們看看該錯誤是否在送出後得到解決。我將嘗試複製該錯誤中不起作用的程式碼。

首先讓我們從錯誤報告中創建該表

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE bug_repeat
   -> (
   ->   name varchar(10),
   ->   price decimal(12,6)
   -> )
   -> ENGINE=MYISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE bug_repeat\G
*************************** 1. row ***************************
      Table: bug_repeat
Create Table: CREATE TABLE `bug_repeat` (
 `name` varchar(10) COLLATE ascii_bin DEFAULT NULL,
 `price` decimal(12,6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin
1 row in set (0.00 sec)

mysql>

接下來，讓我們做一些數據

C:\>type C:\MySQLDBA\bug_test.txt
name,
name,0
,
name,6
name,2
name,
name,0
name,0
name,
name,0

C:\>

讓我們執行 LOAD DATA INFILE

mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
   -> into table test.bug_repeat
   -> fields terminated by ','
   -> lines terminated by '\n';
Query OK, 10 rows affected, 4 warnings (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 4

哎喲，怎麼回事

mysql> show warnings\G
*************************** 1. row ***************************
 Level: Warning
  Code: 1366
' for column 'price' at row 1lue: '
*************************** 2. row ***************************
 Level: Warning
  Code: 1366
' for column 'price' at row 3lue: '
*************************** 3. row ***************************
 Level: Warning
  Code: 1366
' for column 'price' at row 6lue: '
*************************** 4. row ***************************
 Level: Warning
  Code: 1366
' for column 'price' at row 9lue: '
4 rows in set (0.00 sec)

mysql> select * from bug_repeat;
+------+----------+
| name | price    |
+------+----------+
| name | 0.000000 |
| name | 0.000000 |
|      | 0.000000 |
| name | 6.000000 |
| name | 2.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
+------+----------+
10 rows in set (0.00 sec)

mysql>

什麼是 sql_mode ？

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql>

讓我們清空 sql_mode，截斷表並重新載入

mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.00 sec)

mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
   -> into table test.bug_repeat
   -> fields terminated by ','
   -> lines terminated by '\n';
Query OK, 10 rows affected, 4 warnings (0.02 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 4

mysql> show warnings\G
*************************** 1. row ***************************
 Level: Warning
  Code: 1366
' for column 'price' at row 1lue: '
*************************** 2. row ***************************
 Level: Warning
  Code: 1366
' for column 'price' at row 3lue: '
*************************** 3. row ***************************
 Level: Warning
  Code: 1366
' for column 'price' at row 6lue: '
*************************** 4. row ***************************
 Level: Warning
  Code: 1366
' for column 'price' at row 9lue: '
4 rows in set (0.00 sec)

mysql>

讓輸入文件與\N錯誤報告一樣

C:\>type C:\MySQLDBA\bug_test.txt
name,\N
name,0
\N,\N
name,6
name,2
name,\N
name,0
name,0
name,\N
name,0

C:\>

讓我們用 InnoDB 重複所有這些

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE bug_repeat
   -> (
   ->   name varchar(10),
   ->   price decimal(12,6)
   -> )
   -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.05 sec)

mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
   -> into table test.bug_repeat
   -> fields terminated by ','
   -> lines terminated by '\n';
Query OK, 10 rows affected, 4 warnings (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 4

mysql> show warnings\G
*************************** 1. row ***************************
 Level: Warning
  Code: 1366
' for column 'price' at row 1lue: 'N
*************************** 2. row ***************************
 Level: Warning
  Code: 1366
' for column 'price' at row 3lue: 'N
*************************** 3. row ***************************
 Level: Warning
  Code: 1366
' for column 'price' at row 6lue: 'N
*************************** 4. row ***************************
 Level: Warning
  Code: 1366
' for column 'price' at row 9lue: 'N
4 rows in set (0.00 sec)

mysql> select * from bug_repeat;
+------+----------+
| name | price    |
+------+----------+
| name | 0.000000 |
| name | 0.000000 |
| NULL | 0.000000 |
| name | 6.000000 |
| name | 2.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
+------+----------+
10 rows in set (0.00 sec)

mysql>

我使用的是什麼版本的 MySQL ？？？

mysql> show global variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.22                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql>

Linux呢？？？

$ cat /tmp/bug_test.txt
name,\N
name,0
\N,\N
name,6
name,2
name,\N
name,0
name,0
name,\N
name,0

$

登錄mysql並嘗試…

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE bug_repeat
   -> (
   ->   name varchar(10),
   ->   price decimal(12,6)
   -> )
   -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.04 sec)

mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
   -> into table test.bug_repeat
   -> fields terminated by ','
   -> lines terminated by '\n';
ERROR 2 (HY000): File 'C:/MySQLDBA/bug_test.txt' not found (Errcode: 2 - No such file or directory)
mysql> show warnings\G
Empty set (0.00 sec)

mysql> select * from bug_repeat;
Empty set (0.00 sec)

mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.04 sec)

mysql> load data local infile '/tmp/bug_test.txt'
   -> into table test.bug_repeat
   -> fields terminated by ','
   -> lines terminated by '\n';
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

mysql> show warnings\G
Empty set (0.00 sec)

mysql> select * from bug_repeat;
+------+----------+
| name | price    |
+------+----------+
| name |     NULL |
| name | 0.000000 |
| NULL |     NULL |
| name | 6.000000 |
| name | 2.000000 |
| name |     NULL |
| name | 0.000000 |
| name | 0.000000 |
| name |     NULL |
| name | 0.000000 |
+------+----------+
10 rows in set (0.00 sec)

mysql> show global variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.21-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql>

今天的日期 ？？？

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-25 18:48:10 |
+---------------------+
1 row in set (0.01 sec)

mysql>

自送出錯誤報告以來已經一年零一周了，但沒有任何改變。

我對 MySQL 的回答是在十進製欄位中插入 "" 作為 0。如何阻止它？至今仍屹立不倒。

你需要對 MySQL 5.6.23 做這個測試，看看是否有什麼改變。

引用自：https://dba.stackexchange.com/questions/104915

相關問答

Mysql

聯合將整數轉換為天花板的問題（十進制）

  • May 16, 2018
檢視問答
Mysql

離開並組合不為空的結果

  • July 20, 2017
檢視問答
Mysql

欄位 NOT NULL 和 DEFAULT NULL

  • November 24, 2016
檢視問答
Mysql

一般數據庫優化 - 正確的數據類型 - 為什麼要避免 NULL？

  • July 17, 2016
檢視問答
Mysql

MySQL 結果集根據選擇列表更改

  • August 9, 2022
檢視問答
Mysql

MySQL RDS：刪除以前的巨大表而不凍結數據庫？

  • May 21, 2022
檢視問答