Is the mysql Data Type TINYINT (1) or ENUM (true, false) used )?

Source: Internet
Author: User
In my past experience, if I had to decide whether to use the enum data type of mysql, I would give up ENUM () and replace it with tinyint without thinking about it, the reason is what scenarios I have used before and are suitable for use.

In my past experience, if I had to decide whether to use the enum data type of mysql, I would give up ENUM () and replace it with tinyint without thinking about it, the reason is what scenarios I have used before and are suitable for use.

In my past experience, if I had to decide whether to use the enum data type of mysql, I would give up ENUM () and replace it with tinyint without thinking about it, the reason is what I used to use tinyint in my previous scenarios. That is to say, I have not paid much attention to these two field types since I selected tinyint for the first time, today, when developing a superior trademark manager, we need to create a table that stores a large number of fields 0 and 1, because it is a large number of fields 0 and 1, so I hesitated and checked the official documents again. The suggestions are as follows ):

? 72005

Which wocould be the more optimzed way to store a boolean, TINYINT (1) or ENUM ('true', 'false ')? It seems that when storing an enum w/two settings shocould only take up one bit, 0 or 1, but wocould spend more time looking up the enums in the beginning of the query. tinyint (1) however wocould take up 4 bits assuming it ranged 0-9. so it seems if you just wanted to fetch the value, tinyint (1) wocould be faster, but if you are searching through lots of records enum ('true', 'false ') wocould be faster. do y Ou concur ??

"Translation" (because I have never passed the English score, and I have added additional saliva annotations to the translation, I added the double quotation marks to the translation) as follows:

TINYINT (1) or ENUM ('true', 'false ')?

ENUM is used to store enumeration. When there are only two values in the storage, only the width of one digit is used, which is 0 or 1. However, it takes more time to start an enumeration query.

TINYINT (1) occupies the width (0000) of four digits by default)

Therefore, it is concluded that:

For example, if you want to store a value between 0 and 9, we recommend that you use TINYINT (1) to get this value for query purposes,

However, if you want to enumerate a large number of records ("true", "false"), search faster with ENUM ('true', 'false.


Speaking of this ENUM, after reading the network summaries of major technical communities, ENUM is indeed a special field in mysql, I vaguely remember that I used to see some well-known mall systems, such as shopnc, which were used in the past, but I did not go into details, probably because he could set the range of the field, this allows the value to be controlled by the database and has enumeration restrictions (for example, the field only has 0 and 1. If TINYINT (1) is used, 2 may appear, 2 is the stolen data)

However, ENUM also has some difficult problems. For example, during data migration, it is almost impossible for enum to be supported by other databases. If ENUM contains strings, it is even more depressing for other databases, it cannot be set to tinyint or other fields (although enum can store strings, it is still indexed in order internally, for example, 'A', 'B', 'C ', we can also use the index value to obtain the value select * from tbl_name whre enum = 2, which is equivalent to select * from tbl_name where enum = 'B) if you understand the meaning of these two SQL statements, you can also understand why you do not advocate using the enum field.

That is to say, if an unreasonable ENUM field is designed, it would be a nightmare for programmers. For example, the range of an enum field is ('0', '1', '2 ', '3', '4', '5'), and the index corresponding to the enum enumerated value starts from 1. Therefore, insert into table (enum) values (1 ), insert 0 instead of 1.

In addition, if you have designed the enum enumerated field range and used it for a period of time, then add an enumerated value to the field range instead of the final value, this is equivalent to changing the index value of the original range, that is, when you query the value directly (with single quotation marks ), the hidden index values of enum are not used to obtain the results.

If it is a pure numeric type, we suggest using the tinyint field. After all, it only occupies one byte. Even if the stolen data appears, it can be accepted. Unlike enum, if it is a pure numeric range, if the index is changed, you do not know whether the value you queried is correct)

Therefore, we recommend that you use char if the field is a string and the length is fixed. If it is a numeric type, use tinyint. It is safe and stable, and there will be no too many problems even during migration.

So for the convenience of the end, to avoid unnecessary problems, I still use tinyint as I used to in this trademark status expansion table.



Xiao Yu. Jian Feng Yu





This article is from the blog of "fengge Internet research center". Please keep this source

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.