Shulou(Shulou.com)06/01 Report--

The following mainly brings you the knowledge of string types in MySQL column types. I hope these contents can bring you practical use, which is also the main purpose of this article when I edit the knowledge of string types in MySQL column types. All right, don't talk too much nonsense, let's just read the following.

Column type (data type)

The so-called column type, in fact, refers to the data type, that is, a unified classification of data, from a system point of view is to be able to use a unified way of management, better use of limited space.

In SQL, data types are divided into three categories: numerical type, string type and date-time type.

String type

In SQL, string types are divided into six categories: char, varchar, text, blob, enum and set.

Type 1: fixed length string

Fixed-length string: char, that is, the disk (two-dimensional table) determines the storage length of the final data when defining the structure.

Char (L): l represents Length, that is, the length that can be stored, in characters, with a maximum length of 255L

Char (4): means that in a UTF8 environment, it requires 4 bytes, 3 bytes and 12 bytes.

Type 2: variable length string

Variable length string: varchar, that is, when allocating storage space, it is allocated according to the maximum space, but how much is actually used is determined by specific data.

Varchar (L): l stands for Length, the theoretical length is 65536, but there is an extra 1 to 2 bytes to determine the actual length of the storage.

Varchar (10): for example, to store 10 Chinese characters, in the UTF8 environment, it takes 10 "3" 1 "31 bytes.

In fact, if the storage length exceeds 255characters, neither a fixed length string nor a variable length string is used, but the text string text.

How to choose a fixed-length string or a variable-length string?

Fixed-length strings are a waste of disk space, but efficient: if the data are basically the same length, use fixed-length strings, such as ID cards, phone numbers, etc.

Long strings save disk space, but are inefficient: if the data cannot determine the length (different data varies), use variable length strings, such as addresses, names, and so on.

Type 3: text string

If the amount of data is very large, it is usually said that more than 255 characters will be used as a text string.

Text strings are classified according to the stored format and can be divided into:

Text: storing text

Blob: stores binary data (which are actually storage paths), usually not.

Type 4: enumerate strings

Enumerated string: enum, all possible results need to be designed in advance. In fact, the data stored must be one of the specified data.

Enumerate how strings are used:

Definition: enum ('element 1' female'), e.g. enum ('male', 'female', 'secret')

Use: stored data can only be pre-defined data.

Execute the following SQL statement to create an enumeration table for testing:

-- create enumerated table create table my_enum (gender enum ('male', 'female', 'confidential') charset utf8

Then execute the following SQL statement to insert test data into the table my_enum:

-insert test data insert into my_enum values ('male'), ('female'), ('confidential'); insert into my_enum values ('male')

Through the above tests, we can find that there is an advantage of using enumerated strings, that is: standardize the data format, the data inserted into the table can only be a pre-defined data.

In addition, enumerated strings have another function, which is to save storage space (enumerated data usually has an alias), and enumerations actually store numeric values rather than strings themselves.

In MySQL, the system has the function of automatically converting data format. Here, we can prove that the enumerated field stores a numeric value by taking the data out + 0, and if it is a string, the final result is always 0, otherwise it is another value.

-- verify the format select gender + 0 gender from my_enum that enumerated fields actually store

By observing the above results, we can find out the actual law of enumerating elements, that is, numbering elements starting from 1 in the order in which they appear. Next, let's look at the principles of enumerations:

Enumeration in the data specification (definition), the system will automatically establish a number and enumeration elements of the corresponding relationship (put in the log); in the data insertion, the system will automatically convert the string into the corresponding value for storage; in the data extraction, the system will automatically convert the value into the corresponding string for display.

By reading the principles of the above enumerations, we can know that enumerations are not efficient (lower than other types of data), but can standardize data and save storage space.

Category 5: collection string

Collection string: set, similar to enumerations, actually stores numeric values instead of strings.

How the collection string is used:

Definition: set, element list

Use: you can use multiple elements in the element list, separated by commas.

Execute the following SQL statement to create an enumeration table for testing:

Create a collection table create table my_set (hobby set ('Music', 'Movie', 'Travel', 'Food', 'Photography', 'Sports', 'Pet') charset utf8

Then execute the following SQL statement to insert test data into the table my_set:

-insert test data insert into my_set values ('movies, food, pets'); insert into my_set values (3)

Then execute the following SQL statement to view the data in the table my_set:

-- check the data select hobby + 0Pertinent Hobby from my_set

Observe the above results, I believe that most of the children's shoes are also confused! For 3, it's easy to understand, 3-2-1, which corresponds to the number of data in the collection, which is also music and movies; but what the heck is 74? Here, we might as well mark the selected elements in the collection ('music', 'movies', 'travel', 'food', 'photography', 'sports', 'pets') as 1, and those not selected as 0, which is expressed as binary, that is:

0101001

Then reverse the above binary:

1001010

It can be calculated that the decimal number corresponding to the above binary system is 74. 5%.

At this point, I believe you have suddenly realized that every element in the collection string corresponds to a binary bit, in which 1 is selected, 0 is not selected, and finally, in reverse, the decimal number corresponding to this binary number is the value actually stored in its database.

In addition, the order in which elements are inserted in the collection string does not affect, and eventually the system automatically matches the order of the collection, that is:

-insert test data insert into my_set values ('movies, food, travel'); insert into my_set values ('travel, movies, food')

The above two SQL statements produce the same result:

As shown in the figure above, it is clear that our conclusion has been verified.

Finally, the principle of collection is similar to enumeration, so we can come to the same conclusion, that is, it is not efficient to use collection (lower than other types of data), but it can standardize data and save storage space.

For the above knowledge of string types in MySQL column types, you do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.