主页 / dba / 问题 / 104915
Accepted
WAF
Asked: 2015-06-24 10:17:50 +0800 CST

MySQL 在数字列中加载 NULL

  • 772

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

mysql mysql-5.6

1 个回答

  1. Best Answer

    非常简短的回答：没有创建新的数据类型来适应您。

    当我们讨论这个主题时

    让我们尝试简单的 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>

    自提交错误报告以来已经一年零一周了，但没有任何改变。

    My answer to MySQL is inserting "" as 0 in decimal fields. How to stop that? still stands as of today.

    You need to do this test against MySQL 5.6.23 and see if something has changed.

    • 7

相关问题