Shulou(Shulou.com)05/31 Report--

What are the notes for enum insertion in MySQL? I believe many inexperienced people are at a loss about this. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Today, when executing the work order sent by the development, source batch import execution found that there were a lot of warning prompts truncate for column xxxxx. After the import is complete, after using the select query, it is found that a large amount of data was not successfully inserted.

It turns out that the enum field is not in quotation marks.

Conclusion:

The field of enum must be inserted in quotation marks. Otherwise, unexpected problems will arise.

The verification process is as follows:

[none] > use test

[test] > create table T1 (

An int primary key auto_increment

B enum ('4pm, 3m, 2m, 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 implementing INSERT INTO T1 (b) VALUES (4), but the value 1 is inserted, which is completely inconsistent with our actual target result.

| 2 | 4 |-- > here we are executing INSERT INTO T1 (b) VALUES ('4'); 4 with quotation marks is inserted here, which is consistent with our expected result.

+-+ +

Reason:

When a field of type enum is inserted with a numeric value, when it is in quotation marks, the real numeric value is inserted. If you insert without quotation marks, it is actually the inserted key (such as INSERT INTO T1 (b) VALUES (4) in the above example, which inserts the fourth default value of column b, that is, the fourth default value of enum ('4 records, 3 records, 2 records, 1'), that is, the number 1 is finally inserted).

Experiment, insertion under loose sql_mode:

[test] > set session sql_mode=''

[test] > INSERT INTO T1 (b) VALUES (5);-> insert a value outside the range of enum subscript

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 sql_mode is empty, although there is no error when inserting, the query actually has no result. (the b of the 2 rows inserted after checking out is a null value, not NULL).

Continue the experiment, the case of abnormal insertion under strict sql_mode:

[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 that under the strict sql_mode, our exception insertion is reported wrong directly.

ENUM enumeration

Generally, it is not recommended to use it, and it is not easy to expand later. Any value insertion that is not within the scope of the enumeration will report an error, and it is generally appropriate to use tinyint instead of ENUM.

The field values of ENUM are not case-sensitive. Such as insert into tb1 values ("M"); and insert into tb1 values ("m"); have the same effect.

Add:

The storage principle of enum:

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

When creating a field of type enum, we will specify a range for him, such as enum. In this case, a map table of hash structure will be created inside mysql, similar to: 0000-> a map 0001-> bMJ 0002-> c.

When I insert a piece of data, the value bit an or b or c of this field, what he stores in it is not this character, but his index, that is, 0000 or 0001 or 0002.

Similarly, enum's instructions in the mysql manual:

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

1 or 2 bytes, depending on the number of enumerated values (up to 65535 values)

Unless the number of enum exceeds a certain number, the storage space he takes up is always 1 byte.

After reading the above, have you mastered the notes of enum insertion in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.