Summary of MySQL data types and common field attributes _ MySQL

This article mainly introduces the MySQL data type and common field attribute summary. This article summarizes the date and time data types, numerical data types, string data types, and so on. For more information, see Preface

For example, in C ++, how many bytes are required to define the int type and how many bytes are required to define the double type. MySQL strictly controls the data in each column of the table, this is the key to the success of data-driven applications. M Preface

For example, in C ++, how many bytes are required to define the int type and how many bytes are required to define the double type. MySQL strictly controls the data in each column of the table, this is the key to the success of data-driven applications. MySQL provides a set of data types that can be assigned to each column in the table. each type forces data to meet a set of pre-determined rules for this data type, such as the size, type, and format.

Here we will summarize the data types. MySQL data types can be divided into date, time, value, and string. The following is a separate summary.

Date and time data types

MySQL data type	Description
Date	3 bytes, date, format:
Time	3 bytes, time, format: 08: 42: 30
Datetime	8 bytes, date and time, format: 08:42:30
Timestamp	4-byte, automatically storing the record modification time
Year	1 byte, year

Numeric data type

Integer

MySQL data type	Meaning (signed)
Tinyint	1 byte, range (-128 ~ 127)
Smallint	2 bytes, range (-32768 ~ 32767)
Mediumint	3 bytes, range (-8388608 ~ 8388607)
Int	4 bytes, range (-2147483648 ~ 2147483647)
Bigint	8 bytes in the range of (+-9.22*10 to the power of 18)

The above definition is signed. of course, you can also add the unsigned keyword to define it as a non-signed type, so the corresponding value range will be flipped over, for example:

The value range of tinyint unsigned is 0 ~ 255.

Floating point type

MySQL data type	Description
Float (m, d)	4 bytes, single-precision floating point, total m count, d decimal place
Double (m, d)	8 bytes, double-precision floating point, total m count, d decimal place
Decimal (m, d)	Decimal is a floating point number stored as a string

I created a table in MySQL with a column float (5, 3). I did the following experiment:

1. Insert 123.45678. The final query result is 99.999;
2. Insert 123.456 and the final query result is 99.999;
3. Insert 12.34567 and the final query result is 12.346;

Therefore, when using the floating point model, you should pay attention to the traps. the actual result of inserting data into the database should prevail.

String data type

MySQL data type	Description
Char (n)	Fixed length, up to 255 characters
Varchar (n)	Variable length, up to 65535 characters
Tinytext	Variable length, up to 255 characters
Text	Variable length, up to 65535 characters
Mediumtext	Variable length, up to 2 to 24 to 1 characters
Longtext	Variable length, up to 2 to 32 to 1 characters

1. in char (n) and varchar (n) brackets, n represents the number of characters and does not represent the number of bytes. Therefore, when Chinese characters are used (UTF8), m Chinese characters can be inserted, however, it will actually occupy m * 3 bytes.
2. at the same time, the biggest difference between char and varchar is that char occupies n characters regardless of the actual value, while varchar only occupies the space occupied by actual characters + 1, and the actual space is + 1 <= n.
3. the string is truncated after the n settings of char and varchar are exceeded.
4. the upper limit of char is 255 bytes, the upper limit of varchar is 65535 bytes, and the upper limit of text is 65535.
5. the space at the end of the char will be truncated during storage. varchar and text will not.
6. varchar uses 1-3 bytes to store the length. text does not.

Other types

1. enum ("member1", "member2 ″,... "Member65535 ″)
The enum data type defines an enumeration that contains up to 65535 different members. When an enum column is defined, the value of this column is limited to the value declared in the column definition. If the column declaration contains the NULL attribute, NULL is considered a valid value and the default value. If not null is declared, the first member in the list is the default value.

2. set ("member", "member2 ″,... "Member64 ″)
The set data type provides a method to specify zero or multiple values in a set of predefined values, which can contain up to 64 members. The selection of values is limited to the values declared in the column definition.

Data type attribute

The above roughly summarizes the data types in MySQL. of course, the above summary must be incomplete. if you want to summarize these contents in a very comprehensive manner, several articles are not enough. Next we will summarize some common attributes.

1. auto_increment

Auto_increment can assign a unique integer identifier to the newly inserted row. If this attribute is assigned to a column, each newly inserted row is assigned the ID + 1 of the last inserted row.

MySQL requires that the auto_increment attribute be used as the primary key column. In addition, each table can only have one auto_increment column. For example:

The code is as follows:

Id smallint not null auto_increment primary key

2. binary

The binary attribute is only used for char and varchar values. If this attribute is specified for a column, it is sorted in case-sensitive manner. In contrast, when the binary attribute is ignored, the sorting is case-insensitive. For example:

The code is as follows:

Hostname char (25) binary not null

3. default

The default attribute ensures that a constant value is assigned without any value available. this value must be a constant because MySQL does not allow the insertion of function or expression values. In addition, this attribute cannot be used for BLOB or TEXT columns. If the NULL attribute is already specified for this column, the default value is NULL if no default value is specified. Otherwise, the default value depends on the data type of the field. For example:

The code is as follows:

Subscribed enum ('0', '1') not null default '0'

4. index

If all other factors are the same, using indexes is usually the most important step to accelerate database queries. Indexing a column creates an ordered key array for the column, and each key points to its corresponding table row. In the future, you can search for this ordered key array based on the input conditions. compared with searching the entire unindexed table, this will greatly improve the performance.

The code is as follows:

Create table employees
(
Id varchar (9) not null,
Firstname varchar (15) not null,
Lastname varchar (25) not null,
Email varchar (45) not null,
Phone varchar (10) not null,
Index lastname (lastname ),
Primary key (id)
);

You can also use the create index command of MySQL to add an index after creating a table:

The code is as follows:

Create index lastname on employees (lastname (7 ));

This time, only the first seven characters of the name are indexed, because other letters are not required to distinguish different names. Because the performance is better when using a small index, we should try to use a small index in practice.

5. not null

If a column is defined as not null, null values cannot be inserted into the column. We recommend that you always use the not null attribute in important cases because it provides a basic verification to ensure that all necessary values have been passed to the query.

6. null

When a null attribute is specified for a column, the column can be left blank, regardless of whether other columns in the row have been filled. Remember, the exact expression of null is "none", not a null string or 0.

7. primary key

The primary key attribute is used to ensure the uniqueness of the specified row. In the column specified as the primary key, the values cannot be repeated or empty. It is common to assign the auto_increment attribute to a column specified as the primary key, because this column does not have to have any relationship with the row data, but is only a unique identifier. There are two primary keys:

(1) single-field primary key

If each row in the input database already has a unique identifier that cannot be modified, a single-field primary key is usually used. Note that this primary key cannot be modified once it is set.

(2) multi-field primary key

If the uniqueness of any field in the record cannot be ensured, a multi-field primary key can be used. At this time, multiple fields are combined to ensure uniqueness. In this case, it is better to specify an auto_increment integer as the primary key.

8. unique

Columns assigned with the unique attribute ensure that all values have different values, but null values can be repeated. Generally, a column is specified as unique to ensure that all values in the column are different. For example:

The code is as follows:

Email varchar (45) unique

9. zerofill

The zerofill attribute can be used for any numeric type and is used to fill all the remaining field spaces with 0. For example, the default width of the unsigned int is 10. Therefore, when the int value of "zero fill" is 4, it is 0000000004. For example:

The code is as follows:

Orderid int unsigned zerofill not null

Summary completed !!!