Início / dba / Perguntas / 104915
Accepted
WAF
Asked: 2015-06-24 10:17:50 +0800 CST

MySQL carregando NULLs em colunas numéricas

  • 772

MySQL 5.6.23, InnoDB

Estou carregando tabelas de arquivos de texto delimitados por caracteres usando o LOAD DATA INFILEcomando e gostaria que cada campo com um \N, que é o NULLcaractere nessa configuração, colocasse um NULLna tabela. Alguns tipos numéricos têm esse comportamento, enquanto outros colocam uma extensão 0. Estou usando FIELDS TERMINATED BYe algumas colunas obtêm NULLvalores corretamente, portanto, não é um problema de formato de linha fixa.

Estes são os tipos que observei durante os testes:

  • INTinsere NULLs
  • DECIMAL(x,0)insere NULLs
  • DECIMAL(x,y)insere 0.0s
  • FLOATinsere 0s
  • DOUBLE(x,y)insere 0.0s
  • DOUBLEinsere 0s

Todas as colunas em questão são definidas com DEFAULT NULL. Eu sei que várias funções podem converter esses 0s em NULLs. A questão é se existe um tipo de dados que pode lidar com precisão decimal e também inserir NULLs no carregamento.

Além disso, vejo um monte de perguntas relacionadas ao mal-entendido da diferença entre uma string, uma string vazia e um valor nulo. ( exemplo exemplo exemplo ) Este não é o problema, pois os NULLs estão lá e são carregados corretamente na mesma coluna quando eu o redefino como DECIMAL(x,0) e, incorretamente, quando definido como DECIMAL(x,3).

mysql mysql-5.6

1 respostas

  1. Best Answer

    Resposta muito curta: Nenhum novo tipo de dados foi criado para acomodá-lo.

    Enquanto estamos neste assunto

    Vamos tentar SQL simples

    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;

    Isto funciona ???

    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>

    Tudo bem. Funciona com SQL. você está perguntando sobreLOAD DATA INFILE

    Você trouxe uma postagem que respondi: MySQL está inserindo "" como 0 em campos decimais. Como parar isso?

    Vamos ver se esse bug foi corrigido desde que foi enviado. Vou tentar duplicar o código daquele bug que não funcionou.

    Primeiro, vamos criar essa tabela a partir do relatório de bug

    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>

    Em seguida, vamos fazer alguns dados

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

C:\>

    Vamos executar o 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

    ai o que aconteceu

    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>

    Qual é o sql_mode?

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

mysql>

    Vamos apagar o sql_mode, truncar a tabela e recarregar

    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>

    Deixe adulterar o arquivo de entrada \Ncomo o relatório de bug tinha

    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:\>

    Vamos repetir tudo isso com 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>

    Qual versão do MySQL estou usando???

    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>

    E o 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

$

    Entrando no mysql e tentando...

    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>

    Data de hoje ???

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

mysql>

    Já se passou um ano e uma semana desde que o relatório de bug foi enviado e nada mudou.

    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

relate perguntas