Be wary of using the Mysql enum field for reasons _mysql

PHP low-level programming Brothers to look at this problem, I make the following statement, I hope to be able to objectively understand the advantages and disadvantages of this enum field:

Fat brother Point of view:
An enum has advantages. But personally, I think ... Disadvantages more, objectively speaking: the advantage is mainly in the construction of the database when you can put some value to the specification good. The disadvantage is that. Enum is not suitable for PHP. PHP is mainly a weak type, such as: you insert INTO ... set a= 1, you can't know if you want to a= ' 1 ' or a= 1 (a= ' 1 ' is the insertion value 1,a=1 is the first value to insert the enum, especially for PHP weak type, if int, Few people add double quotes in SQL. This is the biggest problem with PHP and MySQL using the enum. So.. Come on, be safe. Simply point direct tinyint.

Single view:
I think there is no merit to the digital enum, it is a nightmare, Boolean tinyint (1) 0,1 status tinyint (1) 1,2,3,4,5,6..tinyint Huan you ~ ~. such as: Audit_result enum (1,2,3), set audit_result = 1; Easy to appear the confusion that fat elder brother says.

Simple point of view:
Less use, is generally replaced by tinyint.

Days Maple point of view:
I think in addition to the status of intuitive no advantages, I generally direct int,tinyint ([1or2or3]) in the end what is the difference? (The following will be a simple discussion, this inside the 1or2or3 difference.) ）

The moderate point of view:
A=1 is the first value to insert an enum, especially if the PHP is weakly typed, and if int is, very few people add double quotes in SQL, which is essentially without quotes.

Harp snail View:
Six kinds of situation: tinyint (1) -1,-2,1,2,3,4

The above points focus on the weak type of language in PHP is not attached to the quotation marks, programmers do not write easily caused by the inserted statement is not the result of their own problems, easy to appear int without quotes caused by inserting a new value instead of the fixed value:

The table structure is as follows:

CREATE TABLE ' enum2tinyint ' (' 
 switchs ' enum (' None ', ' success ', ' fail ', ' delete ', ' skip ', ' 1 ') DEFAULT NULL, 
 ' Switch ' tinyint (1) Not NULL, 
 KEY ' switchs ' (' switchs ') 
engine=innodb DEFAULT Charset=utf8 

Normal quotes Insert enum:

INSERT into ' test ', ' enum2tinyint ' (' switchs ', ' Switch ') VALUES (' 1 ', ' 1 '); 
1 1 

The weak type problem with PHP, especially for the int type, is as follows:
If no quotes are inserted into the enum field, the following (is the first value none):

INSERT into ' test ', ' enum2tinyint ' (' switchs ', ' Switch ') VALUES (1, ' 1 '); 
None 1 

If you do not insert the enum field with the quoted number, the following (is the 2nd value success):

INSERT into ' test ', ' enum2tinyint ' (' switchs ', ' Switch ') VALUES (2, ' 1 '); 
Success 1 

Conclusion: To insert the value of an enum, the field must be enclosed in quotes, without quotes of course a number, and the number is the key, not value.

Exhortations:
1 enum is integral type such errors are very easy to happen, especially PHP weak type, generally new to a person, do not pay attention to the type of enum, you will make mistakes.
2 The database explained clearly, or the choice is all string words, there is nothing, but there are numbers, it is inevitable that beginners make mistakes, the habit of adding quotes is very important.

Final conclusion:
Historical reasons, to change the enum to tinyint program is too big, with no need to change the ~, the new time, as far as possible to use the tinyint good.
This kind of field duplication content is too much, the index constructs not to build, the relation is not big, this kind of in the MySQL index's potential is too low, its query effect is not very good: (English is so translates: https://justwinit.cn/post/1405/).
————————————————————————————————————————————————————————————————————
tinyint (1) and tinyint (4) are the same-127 to 128 or 0 to 256. The unsigned attribute is all positive, and the C language unsigned int is somewhat similar:
tinyint (1)-128 ~ 127
tinyint (1) unsigned 0 ~ 255

Why is the maximum positive number 1 less than the absolute value of the smallest negative number?
First digit is sign bit, 1 is negative
So negative numbers can go to 128, positive numbers only to 127.
-128 Positive 127
such as: 0111 1111 positive, negative 1111 1111

Questions:

in MySQL, int (1) and int (11) are very different, MySQL int, there is a property, UNSIGNED Zerofill after the Zerofill, that is, the definition of the number of digits is not enough to use 0 of the alignment (this may involve the performance of the index): int, length (M) = 5, property =unsigned zerofill (unsigned, 0来 padding digits), 00001,00002.

But what about this tinyint, tinyint (1) and tinyint (4)?

After reading this article: http://www.jb51.net/article/53424.htm

I reckon: MySQL this block for a byte not like int this four byte to carry on the partial byte index to its front part, if said tinyint (1) and tinyint (4) is the same representation scope case, but the MySQL has made the limit of the tinyint digit length digit number to set, Theoretically, its existence in the hard disk is not the same byte is logical, this estimate or to the byte for alignment to facilitate indexing, to enhance the efficiency of the main, L such as: Ike keyword% index is valid, if unified alignment, the index effect will be better? I'm just guessing.
————————————————————————————————————————————————————————————————————

The overall implementation of MySQL on the internet for this enum is as follows:

I. The storage principle of the enum I took a close look at the manual. That is true:
When we create this field, we give him a scope such as an enum (' A ', ' B ', ' C '), and then a hash-structured map table is created within MySQL, similar to the 0000-> a,0001-> b,0002-> c.
When I insert a piece of data, the value of this field A or B or C, he stored in the inside is not the character, but corresponds to his index, that is, that 0000 or 0001 or 0002.
Similarly, the enum is described in the MySQL manual:
ENUM (' value1 ', ' value2 ',...)
1 or 2 bytes, depending on the number of enumerated values (up to 65,535 values)
Unless the number of enums exceeds a certain amount, the storage space he occupies is always 1 bytes.

II) tinyint:
Type byte minimum maximum value
(Signed/unsigned) (Signed/unsigned)
TINYINT 1-128 127
His minimum storage footprint is 1 bytes.

Finally, if you want to use it, Enum, you don't have to use any 0,1,2 to replace the actual string. Even Chinese strings. He does not incur extra overhead on database performance. Because for it, you use the same storage space as the ' 0 ', ' 1 ', ' 2 ' and ' John ', ' Dick ', ' Harry ' datasheet. However, given that we actually use the data from the DB server back to the Web app, so in the network transmission, of course, as far as possible transmission of small data is better. So if you care about this, it's better not to use it.