Reasons to use the MySQL enum field with caution

PHP low-level programming of the brothers to see this problem, I made the following statement, I hope to be able to objectively understand the advantages and disadvantages of this enum field:

Fat Brother View:
Enum has advantages. But personally feel ... Disadvantages more, objectively speaking: the advantages are mainly in the construction of the database when you can put some values to the standard. The disadvantage is that: Enum is not suitable for PHP. The main is that PHP is a weak type, such as: you insert INTO ... set a= 1, you do not know whether 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 PHP weak type, if int, Few people add double quotes in SQL. This is the biggest problem with PHP and MySQL in using enum. So.. Peace of mind. Simply point directly to tinyint.

Single point of view:
I think there is no merit, the number of enum, is a nightmare, Boolean tinyint (1) 0,1 status tinyint (1) 1,2,3,4,5,6..tinyint Huanhuan you ~ ~. such as: Audit_result enum, set audit_result = 1; Prone to the confusion described by fat brother.

Simple point of view:
Less use, is usually replaced with tinyint.

Days Maple View:
I think that in addition to the state of intuitive no advantages, I generally direct int,tinyint ([1or2or3]) What is the difference? (following will be a brief discussion of the 1or2or3 differences in this.) ）

The median point of view:
A=1 is the first value to insert an enum, especially if PHP is weakly typed, and if an int, few people have double quotes in SQL, which is basically unquoted.

Harp snail View:
Six cases: tinyint (1) -1,-2,1,2,3,4

The above various viewpoints focus on PHP, this weakly typed language does not pay attention to the quotation marks, the programmer does not write easy to insert the statement is not the result of their own problems, easy to appear int without quotation marks caused by inserting a new value instead of the fixed value:

The table structure is as follows:

 

1 2 3 4 5

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:

 

1 2	INSERTINTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES(‘1‘, ‘1‘); 1 1

The weak type of PHP problem, especially in the case of type int, is as follows:
If no quotation marks are inserted after the enum field (is the first value of None):

 

1 2	INSERTINTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES(1, ‘1‘); none 1

If no quotation marks are inserted after the enum field (is the 2nd value of Success):

 

1 2	INSERTINTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES(2, ‘1‘); success 1

Conclusion: To insert the value of an enum, the field must be quoted, without quotation marks, of course, the number is key, not value.

Exhortations:
1) enum is an integral type such errors are very easy to happen, especially PHP weak type, generally new to a person, do not pay attention to the enum type, will make mistakes.
2) The database is clear, or can choose all the string words, there is nothing, but there are numbers, there is inevitably a novice to make mistakes, develop the habit of quoting is very important.

Final conclusion:
Historical reasons, to change the enum to tinyint program is too large, with the need to change the ~, later when the new, try to use tinyint good.
This field of repeated content too much, the index is not built, the relationship is not big, this in MySQL called the index is too low, its query effect is not very good

Reasons to use the MySQL enum field with caution