Things to note when enum is inserted in MySQL

Today in the execution of the development of the work order, the source batch import execution time found a lot of warning prompts truncate for column xxxxx. After the import is complete, after you use the Select query, you find that large amounts of data were not successfully inserted.

It was later discovered that the enum field was not quoted as a ghost.

Conclusion:

The field of the enum, which must be enclosed in quotation marks when inserted. Otherwise, unexpected problems can occur.

The verification process is as follows:

[None] > Use test;

[Test] > CREATE TABLE T1 (

A int primary key auto_increment,

b Enum (' 4 ', ' 3 ', ' 2 ', ' 1 ') default ' 3 ');

[Test] > INSERT into T1 (b) VALUES (4);

Query OK, 1 row affected

Time:0.012s

[Test] > INSERT into T1 (b) VALUES (' 4 ');

Query OK, 1 row affected

Time:0.012s

[Test] > SELECT * from T1;

+-----+-----+

|   A | B |

|-----+-----|

|   1 |    1 |    ---> Here we are doing INSERT into T1 (b) VALUES (4); The result is a value of 1, which is completely inconsistent with our actual goal result.

|   2 |    4 |  ---> What we do here is INSERT into T1 (b) VALUES (' 4 '); This inserts the quoted number 4, which is consistent with our expected results.

+-----+-----+

Reason:

When a field of enum type is inserted into a numeric value, the actual value is inserted when quoted. If you do not insert the quotation marks, it is actually the inserted key (as in the example above insert into T1 (b) VALUES (4), insert the fourth default value of column B, that is, take the enum (' 4 ', ' 3 ', ' 2 ', ' 1 ') fourth defaults , that is, the final insertion is the value 1).

Test, insert Condition under loose sql_mode:

[Test] > Set session sql_mode= ";

[Test] > INSERT into T1 (b) VALUES (5); ---> Insert a value beyond the enum subscript range

Query OK, 1 row affected

Time:0.012s

[Test] > INSERT into T1 (b) VALUES (' 5 '); ---> Insert a value that is not allowed in enum

Query OK, 1 row affected

Time:0.011s

[Test] > SELECT * from T1;

+-----+-----+

| A | B |

|-----+-----|

| 1 | 1 |

| 2 | 4 |

|     3 | |

|     4 | |

+-----+-----+

[Test] > SELECT * from t1 where b = ';

+-----+-----+

| A | B |

|-----+-----|

|     3 | |

|     4 | |

+-----+-----+

[Test] > SELECT * from t1 where b is null;

+-----+-----+

| A | B |

|-----+-----|

+-----+-----+

You can see that when the Sql_mode is empty, although there is no error when inserting, but in fact the query is not the result, (check out the inserted 2 rows of B is the ' ' null value, NOT NULL).

To continue the test, strict sql_mode under the condition of abnormal insertion:

[Test] > Set session sql_mode= ' Strict_trans_tables,no_engine_substitution ';

[Test] > INSERT into T1 (b) VALUES (' 5 ');

(1265, U "Data truncated for column ' B ' at row 1")

[Test] > INSERT into T1 (b) VALUES (5);

(1265, U "Data truncated for column ' B ' at row 1")

We can see the strict sql_mode, our abnormal insertion directly error.

Enum enum

Generally not recommended, the latter is not easy to expand. Any value inserted that is not in the scope of the enumeration will be an error, and it is generally more appropriate to replace the enum with tinyint.

The field values of the enum are not case-sensitive. such as insert into TB1 values ("M"); and insert into TB1 values ("M");

Add:

How the enum is stored:

(http://justwinit.cn/post/7354/?utm_source=tuicool&utm_medium=referral)

When creating a field of enum type, we will give him a scope such as enum (' A ', ' B ', ' C '), then MySQL will build a hash structure of the map table, similar to: 0000, a,0001, b,0002 c.

When I insert a piece of data, the value of this field is a bit a or B or C, and he is storing it in a character other than the one that corresponds to his index, which is the 0000 or 0001 or 0002.

Again, the enum is explained in the MySQL manual:

ENUM (' value1 ', ' value2 ',...)

1 or 2 bytes, depending on the number of enumeration values (up to 65,535 values)

Unless the number of enums exceeds a certain number, the storage space he occupies is always 1 bytes.

This article is from the "Vegetable Chicken" blog, please be sure to keep this source http://lee90.blog.51cto.com/10414478/1933603

Things to note when enum is inserted in MySQL