MySQL data type and modifier properties

The role of the data type:
1. Specifying the type of value
2. Specify the space occupied
3. Fixed length (fixed-length) or variable length (variable-length)
4.mysql How to compare and sort the types of values
5. Whether the defined type can be indexed

Data type:
Character
CHAR (length) is stored in character format, is case-insensitive, and fixed-length. Maximum 255 bytes.
VARCHAR (length) becomes longer. Maximum of 65,535 bytes. However, it will take up to 1 (255 characters) or 2 (more than 255 characters) of bytes, and the extra is to use the terminator.

Binary (length) is stored in a two-level format, which is case-sensitive and fixed-length. Maximum 255 bytes.
VARBINARY (length) becomes longer. Maximum 65532 bytes. Additional overhead of 1 or 2 bytes.

BLOB (LENGTH) binary large object. Case-sensitive, maximum 64k,2 bytes of extra overhead
Tinyblob Maximum 255 bytes, 1 bytes of extra overhead
Mediunmblob extra cost for maximum 16m,3 bytes
LONGBLIOB extra cost for maximum 4g,4 bytes


Text (LENGTH) literal format. Case insensitive
Tinytext
Mediumtext
Longtext

Enum enumeration, given a number of values, the range of values must be 1 of these values. A maximum of 65,535 values can be given. There are 65,535 choices.
Set set, given some values, the range of values must be 0 or more of these values, can be arbitrarily combined, but not beyond. A maximum of 64 values are given. The stored method is the index of the stored value. Instead of the value itself.
For example:
Rrtype CHAR (5)
A,ptr,cname,aaaa,mx,ns,soa,srv
Rrtype ENUM (' A ', ' MX ')

For example:
The storage of a collection is the index of the value rather than the value itself.
SET (A,B,C)
A 100
AC 101
BC 011


Modifier Properties:
Not NULL is not empty
NULL is NULL, default
Default Cahr and varchar specific, defaults
CHARACTER Set Character Set
COLLATION the collation, the field inherits from the table, the table inherits from the database, and the library inherits from the default configuration.

Numerical
Exact values
Plastic
TINYINT 1 bytes
SMALLINT 2 bytes
Mediuminit 3 bytes
INT 4 bytes
BIGINT 8 bytes

Modifier Properties:
Auto_increment automatic growth. When inserting new data into a field, the data sequence is automatically generated to complete the data growth. The growth step can be customized. The default is 1, and the step size is 1. Use the Select last_insert_id () function to display the most recent value.
Define the requirements for the field auto_increment:
1. The field must be of type integer.
2. Can only contain positive numbers, cannot contain negative values, can only be defined as unsigned
3. Must be defined as primary key or unique index
4. Must be defined as not NULL

For example:
Mysql>create TABLE Test (ID INT UNSIGNED auto_increment not NULL PRIMARY key,name CHAR (20))

Mysql>select last_insert_id ()


UNSIGNED unsigned only positive and 0.
Not NULL non-null

The decimal precise fixed point number, the total length and the precision are fixed. such as 1.11, etc.
NUMERIC precise fixed-point number.

Bit bitwise STORAGE


Approximate values
Floating point Type
FLOAT (length,num) Single precision 4 bytes
Double (length,num) dual Precision 8 bytes

Note: Length represents a numeric length, and num represents the number of decimal digits, such as float (7,3), which indicates a total length of 7, and a decimal place of 3, for example 1234.567.

Modifier Properties:
Not NULL
Null
DEFAULT
Zerofill extra bit with 0 padding
UNSIGNED


Date Time
Date Yyyy-mm-dd 3 bytes
Time Hh:mm:ss 3 bytes
DATETIME Date Time Yyyy-mm-dd hh:mm:ss 8 bytes
The TIMESTAMP timestamp yyyy-mm-dd Hh:mm:ss, which is converted from 1970-01-01 to the current number of seconds. 4 bytes
Year yyyy or yy 1 bytes

Modifier Properties:
Not NULL
Null
DEFAULT


Boolean
Boolean or bool
0
1

This article is from "Small Private blog" blog, please be sure to keep this source http://ggvylf.blog.51cto.com/784661/1679324

MySQL data type and modifier properties