MySQL data type and characters commonly used segment properties

MySQL has a large number of data types that can be divided into: date and time, numeric value, and string. Let's summarize them separately.

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 stores the time the record was modified
Year	1 bytes, year

Numeric data type

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 (18 +-9.22*10)

The above definitions are signed, of course, you can also add the unsigned keyword, defined as unsigned type, then the corresponding value range 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
Double (M, D)	8 bytes, double-precision floating-point, m total number, D-Decimal
Decimal (M, D)	Decimal is a floating-point number stored as a string

I built a table in MySQL with a column named float (5, 3), and the following tests were done:

1. Insert 123.45678, the last query results are 99.999;
2. Insert 123.456, the last query result is 99.999;
3. Insert 12.34567, the last query result is 12.346;

Therefore, in the use of floating-point type, but also to pay attention to the trap, to insert the actual results in the database will prevail.

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

The 1.char (n) and varchar (n) brackets mean the number of characters, not the number of bytes, so when Chinese is used (UTF8) means you can insert M Chinese, but it actually consumes m*3 bytes.
2. At the same time char and varchar The biggest difference is that char regardless of the actual value will occupy n characters space, and varchar will only occupy the actual character should occupy +1 of the space, 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 bound of varchar is 65535 bytes, and the text is capped at 65535.
5.char will truncate the trailing spaces when stored, 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 with a maximum of 65,535 distinct members. When a column of 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 attribute, then NULL is considered to be 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 way to specify 0 or more values in a predefined set of values, which includes a maximum of 64 members. The selection of a value is limited to the value declared in the column definition.

Data Type Properties

The above probably summarizes the data type 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 is a summary of some of the commonly used properties.

1.auto_increment

The 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 last inserted id+1.

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

The code is as follows:

null auto_increment PRIMARY key

2.binary

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

The code is as follows:

hostname char (+) binary NOT NULL

3.default

The default property ensures that if no value is available, a constant value must be assigned, because MySQL does not allow the insertion of a function or an expression value. Additionally, this property cannot be used with a BLOB or text column. If you have specified a null property for this column, the default value will be NULL if no default value is specified, otherwise the default value will depend 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 an index is often the most important step to speed up database queries. Indexing a column creates an ordered array of keys for that column, with each key pointing to its corresponding table row. This ordered array of keys can be searched later for the input criteria, which will be greatly improved in terms of performance compared to searching the entire unindexed table.

The code is as follows:

CREATE TABLE employees (ID varchar (9) Not null,firstname varchar (a) not null,lastname varchar (+) not null,email varchar (4 5) Not null,phone varchar (TEN) not Null,index LastName (LastName), primary key (ID));

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

The code is as follows:

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

This time only the first 7 characters of the name are indexed, because other letters may not be required to distinguish different names. Because the performance is better 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 the 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 other columns in the row have already been populated. Remember, the null exact argument is "none", not an empty string or 0.

7.primary Key

The primary key property is used to ensure uniqueness of the specified row. In a column that is specified as the primary key, the value cannot be duplicated or empty. It is common to assign the Auto_increment property to a column that is specified as a primary key, because this column does not have to have any relation to the row data, but only as a unique identifier. The primary key is divided into the following two kinds:

(1) Single field primary key

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

(2) Multi-field primary key

You can use a multi-field primary key if none of the fields in the record are guaranteed to be unique. At this point, multiple fields are joined together to ensure uniqueness. If this is the case, specifying a auto_increment integer as the primary key is a better approach.

8.unique

The column that is given the unique property ensures that all values have different values, except that null values can be duplicated. A column is typically specified as unique to ensure that all values of the columns are different. For example:

The code is as follows:

Email varchar Unique

9.zerofill

The Zerofill property can be used for any numeric type and fills all remaining field spaces with 0. For example, the default width for unsigned int is 10, so when the int value of "0 padding" is 4 o'clock, it is represented as 0000000004. For example:

The code is as follows:

OrderID int unsigned zerofill NOT null

　　

MySQL data type and characters commonly used segment properties