Reasons for using the mysql enum field with caution

PHP's low-level programming brother came to look at this problem. I wrote the following transcript and expected to objectively understand the advantages and disadvantages of this enum field: Yi's opinion: enum has advantages. But I personally think... More disadvantages: objectively speaking, the main advantage is that some values can be standardized during database creation. Disadvantage .. Enum is not suitable for PHP. Mainly

PHP's low-level programming brother came to look at this problem. I wrote the following transcript and expected to objectively understand the advantages and disadvantages of this enum field: Yi's opinion: enum has advantages. But I personally think... More disadvantages: objectively speaking, the main advantage is that some values can be standardized during data warehouse creation. Disadvantage .. Enum is not suitable for PHP. Mainly

The PHP low-level programming team looks at this issue as follows. I hope to objectively understand the advantages and disadvantages of this enum field:

YAN Ge's point of view:
Enum has advantages. But I personally think... More disadvantages: objectively speaking, the main advantage is that some values can be standardized during data warehouse creation. Disadvantage .. Enum is not suitable for PHP. PHP is a weak type, for example, insert ..... set a = 1, you can't know if you want a = '1' or a = 1 (a = '1' is the insert value 1, a = 1 is the first value to insert enum, especially for weak php types. If int is used, few double quotation marks are added to SQL .), This is the biggest problem in using enum in PHP and mysql. So .. Feel at ease. Simply click tinyint.

Single point of view:
I don't think there are any advantages. For the digital enum, it is simply a nightmare. boolean tinyint (1) status tinyint (1), 5, 6 .. tinyint is fond of you ~~. For example: audit_result enum (1, 2, 3), set audit_result = 1;... it is easy to see the obfuscation described by brother Yi.

Simple View:
Rarely used, usually replaced by tinyint.

Tian Feng's point of view:
I think there are no advantages in addition to the intuitive status. What are the differences between I direct int and tinyint ([1or2or3? (The difference between 1or2or3 is briefly discussed later .)

Moderate opinion:
A = 1 is the first value to insert enum, especially for weak php types. If int type, few double quotation marks are added to SQL statements, they are basically not enclosed by quotation marks.

Point of View:
In six cases: tinyint (1)-1,-, 4

The above views focus on the fact that PHP is a weak language that does not pay attention to quotation marks. programmers do not write statements that may easily cause insertion of statements that are not the expected results, when int is easy to appear, no quotation marks are used to insert a new value instead of a 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 

Insert enum with quotation marks normally:

INSERT INTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES ('1', '1'); 1 1 

PHP weak type problem, especially for int type, the practice is as follows:
If the enum field is not inserted with quotation marks, it is as follows (the first value is none ):

INSERT INTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES (1, '1'); none 1 

If the enum field is not inserted with quotation marks, it is as follows (the value is 2nd success ):

INSERT INTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES (2, '1'); success 1 

Conclusion: To insert the value of enum, the field must be enclosed by quotation marks. Without quotation marks, the value is a number, and the number is a key, not a value.

Advise:
1) enum is an integer type and is prone to errors, especially when php is weak. It is generally new to a person who does not pay attention to the enum type.
2) If the database is clear, or if all the options are strings, there is nothing left, but there are numbers in it, it is inevitable that new users make mistakes, it is very important to develop the habit of adding quotation marks.

Conclusion:
For historical reasons, it is too much to change the enum to tinyint, and there is no need to change it if it is used ·~, Try tinyint later.
This type of field contains too many duplicates, and the index cannot be created, which has little to do with it. In mysql, the index is too low, and the query effect is not very good: (the English translation is as follows: https://justwinit.cn/post/1405 ).
--------------------------------------------------------------------
Tinyint (1) and tinyint (4) are both-127 to 128 or 0 to 256. The unsigned attributes are all positive, which is similar to the C language unsigned int:
Tinyint (1)-128 ~ 127
Tinyint (1) unsigned 0 ~ 255

Why is the maximum positive number less than the absolute value of the minimum negative number 1?
The first digit is the symbol bit. 1 indicates a negative number.
Therefore, a negative number can reach 128, and a positive number can only reach 127.
-128-127
For example: 0111 1111 positive number, negative 1111 1111

Question:

In Mysql, int (1) and int (11) are very different. In mysql, int has an attribute: ZEROFILL after unsigned zerofill, if the number of defined digits is not enough, use zero-padding to align them (this may involve index performance): int, length (M) = 5, attribute = unsigned zerofill (UNSIGNED, fill the number of digits with 0.

But what about tinyint? Is tinyint (1) the same as tinyint (4?

After reading this article: http://www.chengxuyuans.com/article/53424.htm

I guess: mysql does not need to perform partial byte indexes on the first part of a byte like int. If tinyint (1) and tinyint (4) when the range is the same, mysql imposes configurable limits on the length and digits of the tinyint number. Theoretically, it is logical to say that the bytes in the hard disk are different, this estimation aims to align the bytes to facilitate the indexing and so on, so as to improve efficiency. l for example, the ike keyword % index is valid. Will the index effect be better if it is aligned in a unified manner? I guess.
--------------------------------------------------------------------

The general implementation of mysql with this enum found on the internet is as follows:

1) I have carefully checked the enum storage principle. Yes:
When this field is created, we will specify a range for it, such as enum ('A', 'B', 'C '), in this case, mysql creates a map table with a hash structure, for example, 0000-> a, 0001-> B, 0002-> c.
When I insert a piece of data with a value of a, B, or c, the field is not stored in this character, but corresponds to its index, that is, 0000, 0001, or 0002.
Similarly, the description of enum 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 enum exceeds a certain number, the storage space occupied by enum is always 1 byte.

2) tinyint:
Maximum Minimum value of Type bytes
(Signed/unsigned) (signed/unsigned)
TINYINT 1-128 127
Its minimum storage space is also 1 byte.

Finally, since Enum is used, it is not necessary to use 0, 1, 2 to replace the actual string. Or even a Chinese string. It does not overhead the database performance. For it, you use '0', '1', '2', and 'zhang san', 'Li si', and 'wang wu' Data Tables occupy the same storage space. However, considering that data needs to be transferred back from the db server to the web app during actual application hours, it is better to transmit small data as much as possible during network transmission. So if you care about this, you still don't need it.