Numeric values, usually tinyint, int, bigint and bigint.

Floating point/fixed point, usually floating point, double precision, decimal and related synonyms.

String, usually expressed as char, varchar.

Time and date, usually date, date time, time and time stamp.

Binary, usually binary, varbinary.

Bit type

enumeration type

Collection type

Large objects, such as text and blob

Json document type

1. numeric type (not data type, don't make a mistake) If it is used to store integers, choose different types according to different ranges.

The above are some examples of integer selection. Integer has the widest application, and can be used to store numbers, timestamps and other types of codes converted into numbers, such as IPv4. Example 1 uses int32 to store IPv4 addresses, which saves space than simply using strings. Table x 1, field ipaddr, use the function inet_aton, and use the function inet_ntoa when searching.

Check the disk space occupation, t3 occupies the largest, and t 1 occupies the smallest. Therefore, if there is a fixed upper limit on the storage range of integers, it is recommended to choose the smallest type, which is also applicable to other types. Root @ YTT-PC:/var/lib/MySQL/3305/YTT # ls-sihl total dose 3.0g354182581m-rw-r-1mysql860m65438+February/kloc. -1MySQL MySQL 988m65438+February101:38t2.ibd35418231.2g-rw-r-65438.

Second, floating point number/fixed point number first? Floating-point numbers, float and double all represent floating-point numbers. The simple difference is that by default, float occupies 4 bytes. P in float(p) represents the minimum precision of integer bits. If p > 24 is directly converted into double, accounting for 8 Byte. The maximum value of p is 53, but the calculation of the maximum value is not accurate. Besides? Fixed points, including decimal and synonym values, are stored in integers and decimal places respectively, and the maximum effective precision cannot exceed 65. So the difference from float lies in accurate storage, and the best definition of accurate storage or accurate calculation is decimal. Example 3 creates a table y 1 and assigns different types to fields F 1, F2 and F3 respectively. MySQL-(ytt/3305)-& gt； Create table y 1(f 1 floating point number, f2 double precision, f3 decimal (10,2)); The query is normal, and 0 lines are affected (0.03 seconds).

Third, character types are as versatile as plastic surgery. Used to store characters, strings, MySQL all unknown types. It can be simply said that it is universal!

Char( 10) indicates the maximum support for 10 character storage. Although varhar( 10) can store as many characters as char( 10), the difference is that the varchar type stores the actual size, and the theoretical storage size of char is fixed. The specific number of bytes is related to the character set. Example 4: For example, in the following table t4, the two fields C 1 and C2 are char and varchar, respectively. MySQL-(ytt/3305)-& gt； Create table t4 (c 1 char(20), c2varchar (20)); The query is normal, and 0 lines are affected (0.02 seconds).

So when choosing char and varchar, we should pay attention to whether there is a suitable range of values. For example, for fixed-length values, you must choose char；; Uncertain value, select varchar.

IV. Date types Date types include date, time, date and time, time stamp and year. The year is 1 byte, and the date is 3 bytes.

Time, time, timestamp, date and time account for 3 bytes, 4 bytes and 8 bytes respectively without decimal places; The fractional part of the disk occupation is calculated separately, as shown in the following table.

Please click to enter a picture description.

Please click to enter a picture description.

Note: the timestamp is an integer stored in int32, and the value range is'1970-01-0100: 01.000000' to' 2038-01. The date and time range is "1000-0100: 00.000000" to "nine thousand nine hundred and ninety-nine-12-3123: 59: 59.999". ?

To sum up, the selection of such dates follows the following principles:

1. If the time may be out of the timestamp range, datetime is preferred. 2. If you need to obtain the year value separately, such as partition by year, search by year, etc. , it is best to add a year type to the table to participate. 3. If you need to get the date or time separately, it is better to store it separately instead of simply using datetime or timestamp. When searching later, add function filtering to avoid the extra consumption caused by adding SQL writing later.

4. If there is a similar need to save milliseconds, it is best to use the characteristics of the time type itself instead of using the character type directly. Additional resources consumed by MySQL internal type conversion also need to be considered.

Example 5

Establish a table t5, and leave all these fields that may be needed, so that it is easy to write SQL statements in the future.

Of course, this situation will take up extra disk space. If you want to compromise between ease of use and large space occupation, you can use MySQL virtual columns for real-time calculation. For example, suppose the c5 field does not exist and you want to get the result of c5. MySQL-(ytt/3305)-& gt； When changing table t5, delete c5, and the generation year of adding c5 will always be (year(c 1)). Query OK, 1 Line affected (2.46 seconds) Record: 1? Duplicate item: 0? Warning: 0

Verb (abbreviation for verb) binary type

Binary and varbinary correspond to the binary storage of char and varchar, and the related characteristics are the same. The differences are as follows:

Binary (10)/varbinary (10) represents not the number of characters, but the number of bytes.

Line terminators are different. The line terminator of char is \0, and the line terminator of binary is 0x00.

Because it is binary storage, the character encoding and sorting rules are directly invalid.

Example 6

Let's look at this simple example of binary access, or the previous variable @ a.

Remember! Here, the number of bytes occupied by @a should be calculated in advance to prevent memory overflow.

Six, bit type

Bit is a bit type stored in MySQL, which supports 64 bits at most, and is directly stored in binary mode, and is generally used to store information of status classes. Such as gender, authenticity and so on. Has the following characteristics:

1. For bit (8), if 1 bit is simply stored, 000000 1 is filled with 0 on the left. 2. Decimal data can be directly filtered when querying. 3. If this field is indexed, MySQL will not do its own type conversion, and can only use binary to filter.

Example 7

Create table c 1, and define one bit for the field gender. MySQL-(ytt/3305)-& gt； Create table c 1 (gender bit (1)); The query is normal, and 0 lines are affected (0.02 seconds).

MySQL-(ytt/3305)-& gt； Select cast (gender is unsigned)? F 1' comes from c1; + - +| f 1？ |+| 0 ||1|++2 lines in the set (0.00 seconds)

The same is true for filtering data, whether it is binary or direct decimal. MySQL-(ytt/3305)-& gt； Select conv (gender, 16, 10) as the gender \->; From c 1, where gender = b'1'; ? +-+| Gender |+-+| 1? |+-+65438+ 0 line (0.00 second) MySQL-(YTT/3305)-> in the set; Select conv (gender, 16, 10) as the gender \->; From c 1, where gender ='1'; +-+| Gender |+-+| 1? |+-+65438+ 0 line in the set (0.00 second)

In fact, such a scene can also be defined as char(0), which is also a very optimized usage similar to bit.

MySQL-(ytt/3305)-& gt； Create table C2 (genderchar (0)); The query is normal, and 0 lines are affected (0.03 seconds).

Now I give a simple test data to table c 1.

MySQL-(ytt/3305)-& gt； Select count (*) from c 1; +-+| Count (*) |+-| 33554432 |+-+65438+0 row in the set (1.37 seconds)

Insert all data of c 1 into c2.

MySQL-(ytt/3305)-& gt； Insert into c2 select if(gender = 0,'', null) from c1; Query OK, 33554432 lines affected (2 minutes 18.80 seconds) Record: 33554432? Duplicate item: 0? Warning: 0

The disk usage of the two tables is similar. Root @ YTT-PC:/var/lib/MySQL/3305/YTT # ls-sihl total dose is1.9g4085684 933m-rw-r-1mysql932m1/. -1MySQL MySQL 916m65438+February1110: 22c2.ibd.

The retrieval method is slightly different, but the efficiency is similar. Therefore, the personality type deserves to be a universal type.

Seven, enumeration types

Enumeration type, namely enum. It is suitable to plan all known values in advance, and it is best not to add new values in the future. Enumeration types have the following characteristics:

1. The maximum occupancy is 2 bytes. 2. Support up to 65535 different elements. 3.MySQL stores the following objects in the background, namely tinyint or smallint, with subscripts starting from 1. 4. Sort by subscript, not by data type of internal elements. So pay special attention to this.

Example 8

Create table t7. MySQL-(ytt/3305)-& gt； Create table t7(c 1 enum('mysql',' oracle',' dble',' postgresql',' mongodb',' redis',' db2',' SQL Server'); The query is normal, and 0 lines are affected (0.03 seconds).

Eight, set type

Collection types Collections are similar to enumerations, but you need to know in advance how many elements there are. A collection has the following characteristics:

1. The maximum occupancy is 8 bytes, int64. 2. Internal storage is in the form of binary bits, and the corresponding subscripts are 1, 2, 4, 8, ..., power (2, 63) if viewed in decimal. 3. Support up to 64 different elements, and directly insert and delete duplicate elements. 4. Elements can be inserted in combination. For example, the subscript 1 and 2 can be inserted together, and 3 can be inserted directly.

Example 9

The c7 field c 1 of the table is defined as a set type, which contains 8 values, that is, the maximum value in the table below is pow (2,7).

MySQL-(ytt/3305)-& gt； Create table c7(c 1 set ('mysql',' oracle',' dble',' postgresql',' mongodb',' redis',' db2',' SQL Server'); The query is normal, and 0 lines are affected (0.02 seconds).

Insert all combinations of 1 to 128.

MySQL-(ytt/3305)-& gt； Use recursive YTT _ number (CNT) as (select1ascnt union all select CNT+1from YTT _ number where CNT <: pow(2，7))SELECT * FROM ytt _ number； Query OK, 128 lines affected (0.0 1 sec) records: 128? Duplicate item: 0? Warning: 0

Nine, the usage of data types in stored functions

Except for explicitly declared variables, the data type of default session variables in functions is weak, and they can be converted at will with different given values.

Example 10

Defines a function that returns the product of two given parameters. There are two variables in the definition, one is that v_tmp is explicitly defined as int64, and the other is @vresult, which changes the type with a given value.

Simply make a phone call.

MySQL-(ytt/3305)-& gt； Select YTT _ sample _ data _ type (1111,222)' result'; +-+| Results? |+-+| The result is:' 246642'. |+-+65438+ 0 line in the set (0.00 second)

abstract

This paper briefly introduces the basic data types of MySQL, and sorts out these types with some easy-to-understand examples. In the actual scenario, we recommend choosing the most suitable type, and do not recommend the principle of simple maximization of all data types. For example, you can use varchar( 100) instead of varchar( 1000).