Mysql data type and the commonly-_mysql section attribute summary

Objective

Like C + +, how many bytes are required to define the int type, and how many bytes are required to define the double type, MySQL also controls the data in each column of the table tightly, which is key to the success of the data-driven application. M preface

Like C + +, how many bytes are required to define the int type, and how many bytes are required to define the double type, MySQL also controls the data in each column of the table tightly, which is key to the success of the data-driven application. MySQL provides a set of data types that can be assigned to each column in a table, each of which forces the data to meet a predetermined set of rules for that data type, such as size, type, and format.

Here we summarize the data types first. The data types in MySQL are large, and can be divided into: date and time, numeric values, and strings. The following is divided to summarize.

Date and time data types

MySQL data type	Meaning
Date	3 bytes, date, format: 2014-09-18
Time	3 bytes, time, format: 08:42:30
Datetime	8 bytes, date time, format: 2014-09-18 08:42:30
Timestamp	4 bytes, automatically storing the time the record was modified
Year	1 bytes, year

Numeric data types

Integral type

MySQL data type	Meaning (Signed)
tinyint	1 bytes, range ( -128~127)
smallint	2 bytes, range ( -32768~32767)
Mediumint	3 bytes, range ( -8388608~8388607)
Int	4 bytes, range ( -2147483648~2147483647)
bigint	8 bytes, range (+-9.22*10 18)

Defined above are all signed, of course, can also add unsigned keyword, defined as unsigned type, then the corresponding range of values will be overturned, such as:

The value range of tinyint unsigned is 0~255.

Floating-point type

MySQL data type	Meaning
Float (M, D)	4 bytes, single-precision floating-point type, m total number, D decimal digit
Double (M, D)	8 byte, double-precision floating-point type, m total number, D decimal digit
Decimal (M, D)	Decimal is a floating-point number that is stored as a string

I set up a table in MySQL, one listed as float (5, 3); The following experiments were done:

1. Insert 123.45678 and the result of the final query 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 floating-point type, we should pay attention to traps, to insert the actual results in the database.

String data type

MySQL data type	Meaning
CHAR (n)	Fixed length, up to 255 characters
VARCHAR (n)	Variable length, up to 65,535 characters
Tinytext	Variable length, up to 255 characters
Text	Variable length, up to 65,535 characters
Mediumtext	Variable length, up to 2 of 24 square-1 characters
Longtext	Variable length, up to 2 of 32 square-1 characters

1.char (n) and varchar (n) in brackets n represents the number of characters, does not represent the number of bytes, so when the use of Chinese (UTF8) means that you can insert M Chinese, but actually occupy m*3 bytes.
2. At the same time char and varchar The biggest difference is that char regardless of the actual value will occupy the space of n characters, and varchar will only occupy the actual characters should occupy space +1, and the actual space +1<=n.
3. After the n setting of char and varchar is exceeded, the string is truncated.
The upper limit of 4.char is 255 bytes, the upper limit of varchar is 65535 bytes, and text is capped at 65535.
5.char at the time of storage will truncate the trailing spaces, varchar and text will not.
6.varchar will use 1-3 bytes to store the length, text will not.

Other types

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

2.set ("member", "Member2″, ...") "Member64″)
The Set data type provides a method for specifying 0 or more values in a predefined set of values, which includes up to 64 members. The selection of the value is limited to the value declared in the column definition.

Data Type Properties

The above probably summed up the data types in MySQL, of course, the above summary is certainly not comprehensive, if you want a very comprehensive summary of these content, several articles are not enough. Here's a summary of some common properties.

1.auto_increment

Auto_increment can assign a unique integer identifier to the newly inserted row. Assigning this property to a column assigns each newly inserted row a value of the id+1 that was last inserted.

MySQL requires the Auto_increment property to be used as a primary key column. In addition, only one auto_increment column is allowed per table. For example:

Copy Code code as follows:

ID smallint NOT NULL auto_increment primary key

2.binary

The binary property is used only for char and varchar values. When this property is specified for a column, it is sorted in a case-sensitive manner. Conversely, when the binary property is ignored, the case-insensitive method is used to sort. For example:

Copy Code code as follows:

hostname char () binary NOT NULL

3.default

The default property ensures that when no value is available, a constant value must be a constant, because MySQL does not allow the insertion of functions or expression values. In addition, this property cannot be used in a BLOB or text column. If you have specified a null property for this column, the default value will be null if you do not specify a default value, otherwise the default value will depend on the data type of the field. For example:

Copy Code code as follows:

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

4.index

If all other factors are the same, to speed up database queries, using an index is usually the most important step. Indexing a column creates an ordered array of keys for the column, with each key pointing to its corresponding table row. This ordered array of keys can then be searched for the input criteria, which will greatly improve performance compared to searching the entire index table.

Copy Code code as follows:

CREATE TABLE Employees
(
ID varchar (9) NOT NULL,
FirstName varchar () NOT NULL,
LastName varchar NOT NULL,
Email varchar () NOT NULL,
Phone varchar NOT NULL,
Index LastName (LastName),
Primary KEY (ID)
);

We can also use the MySQL CREATE INDEX command to increase the index after creating the table:

Copy Code code as follows:

CREATE INDEX LastName on employees (LastName (7));

This time it only indexes the first 7 characters of the first name, because it may not require other letters to distinguish between different names. Because of the better performance when using smaller indexes, you should try to use small indexes in practice.

5.not NULL

If you define a column as NOT NULL, you will not be allowed to insert a null value into the column. It is recommended that you always use the NOT NULL property in important cases because it provides a basic validation to ensure that all necessary values have been passed to the query.

6.null

When you specify a null property for a column, the column can remain empty, regardless of whether the other columns in the row have been filled. Remember, NULL is the exact term "none", not an empty string or 0.

7.primary Key

The primary key property is used to ensure uniqueness of the specified row. The value cannot be duplicated or empty in a column that is specified as a primary key. Assigning a auto_increment property to a column that specifies a primary key is common because the column does not have to have any relationship to the row data, but rather as a unique identifier. The primary key is divided into the following two kinds:

(1) Primary key of single field

If each row entered into the database already has a unique identifier that cannot be modified, a single field primary key is typically used. Note that this primary key can no longer be modified once it is set.

(2) Multi-field primary key

You can use a multiple-field primary key if none of the fields in the record are guaranteed to be unique. At this point, multiple fields join together to ensure uniqueness. If this happens, it is a better idea to specify a auto_increment integer as the primary key.

8.unique

A column that is assigned a unique property ensures that all values have different values, except that null values can be duplicated. It is generally specified that a column is unique to ensure that all the values of the columns are different. For example:

Copy Code code as follows:

Email varchar (unique)

9.zerofill

The Zerofill property can be used for any numeric type, filling all remaining field space with 0. For example, the default width of an unsigned int is 10, so when the int value of 0 fills is 4 o'clock, it will represent 0000000004. For example:

Copy Code code as follows:

OrderID int unsigned zerofill NOT null

Summary Complete!!!