What are the data types of MySQL? MySQL Data type detailed

This article lists the MySQL data type list, which consists of five main categories: integer type, floating-point type, String type, date type, and other data types, which are explained in detail in the following five data types. There are also the lengths and ranges of MySQL data types, along with basic principles for using recommendations and selecting data types.

First, MySQL data type

These include the following five major categories:

Integer types: BIT, BOOL, TINY int, SMALL int, MEDIUM int, int, BIG int

Floating-point type: float, DOUBLE, DECIMAL

String types: CHAR, VARCHAR, TINY text, text, MEDIUM text, Longtext, TINY blob, blob, MEDIUM blob, LONG blob

Date types: Date, DateTime, TimeStamp, time, year

Other data types: BINARY, VARBINARY, ENUM, SET, Geometry, point, MultiPoint, LineString, multilinestring, Polygon, GeometryCollection, etc.

1. Integral type

MySQL data type	Meaning (Signed)
tinyint (m)	1 byte range ( -128~127)
smallint (m)	2 byte range ( -32768~32767)
Mediumint (M)	3 byte range ( -8388608~8388607)
Int (m)	4 byte range ( -2147483648~2147483647)
BigInt (M)	8 byte Range (18 +-9.22*10 of the X-square)

If unsigned is added to the value range, the maximum value is doubled, such as the tinyint unsigned value range (0~256).

m in int (m) is the display width in the result set of the select query, does not affect the actual range of values, does not affect the width of the display, do not know what the use of this m.

2. Float type (float and double)

MySQL data type	Meaning
Float (m,d)	Single-precision floating-point 8-bit precision (4 bytes) m total number, D decimal place
Double (m,d)	Double-precision floating-point 16-bit precision (8 bytes) m total number, D decimal place

Set a field defined as float (6,3), if you insert a number 123.45678, the actual database is 123.457, but the total number is actually the same, that is, 6 bits. The integer portion is 3 bits maximum, if the number of inserts is 12.123456, 12.1234 is stored, if 12.12 is inserted, 12.1200 is stored.

3. Fixed-point number

Floating-point types hold approximate values in the database, while the fixed-point type stores the exact values in the database.

The decimal (m,d) parameter m<65 is the total number, d<30 and d<m is the decimal place.

4. String (Char,varchar,_text)

MySQL data type	Meaning
CHAR (n)	Fixed length, up to 255 characters
VARCHAR (n)	Fixed 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

Char and varchar:

1.char (n) If the number of characters in the deposit is less than n, then the space is appended to it, and then the space is removed when queried. So the char type stores a string with no spaces at the end, and varchar is not limited to this.

2.char (n) fixed length, char (4) regardless of whether a few characters are deposited, will occupy 4 bytes, varchar is the actual number of characters stored in + 1 bytes (n<=255) or 2 bytes (n>255),

So varchar (4), a deposit of 3 characters will occupy 4 bytes.

The string retrieval speed of the 3.char type is faster than the varchar type.
varchar and text:

1.varchar can specify that N,text cannot be specified, the internal storage varchar is the actual number of characters stored in + 1 bytes (n<=255) or 2 bytes (n>255), text is the actual number of characters + 2 characters

Section.

The 2.text type cannot have a default value.

3.varchar can create indexes directly, text creates indexes to specify the number of characters before. varchar queries are faster than text, and the index of text does not seem to work in the case of all indexes being created.

5. Binary data (_BLOB)

1._blob and _text are stored in different ways, _text stored in text, English storage is case-sensitive, and _blob is stored in binary mode, regardless of case.

The data stored by 2._blob can only be read out in its entirety.

3._text can specify a character set, _blo does not specify a character set.

6. Date and Time type

MySQL data type	Meaning
Date	Date ' 2008-12-2 '
Time	Time ' 12:25:36 '
Datetime	Date Time ' 2008-12-2 22:06:44 '
Timestamp	Automatically store record modification times

If you define a field that is timestamp, the time data in the fields is automatically refreshed when the other fields are modified, so the field of this data type can hold the last time the record was modified.

Properties of the data type

mysql keyword	Meaning
Null	Data columns can contain null values
Not NULL	Data columns are not allowed to contain null values
DEFAULT	Default value
PRIMARY KEY	Primary key
Auto_increment	Auto-increment for integer type
UNSIGNED	No sign
CHARACTER SET Name	Specify a character Set

Ii. length and range of MySQL data types

List of data types and byte lengths:

Data type	byte length	Scope or usage
Bit	1	unsigned [0,255], signed [-128,127], Day margin blog notes: Bit and bool Boolean all occupy 1 bytes
TinyInt	1	Integer [0,255]
SmallInt	2	unsigned [0,65535], signed [ -32768,32767]
Mediumint	3	unsigned [0,2^24-1], signed [ -2^23,2^23-1]]
Int	4	unsigned [0,2^32-1], signed [ -2^31,2^31-1]
BigInt	8	unsigned [0,2^64-1], signed [ -2^63, 2^63-1]
Float (M,D)	4	Single-precision floating-point number. The Day Edge Blog reminds you that D is precision, if d<=24 is the default float, if d>24 is automatically converted to double type.
Double (M,D)	8	Double-precision floating point.
Decimal (M,D)	M+1 or m+2	unpackaged floating-point numbers, used similar to float and double, the day margin blog reminds you that if you use the Decimal data type in ASP, the decimal that is read directly from the database may need to be converted to a float or double type before the operation.
Date	3	displayed in YYYY-MM-DD format, for example: 2009-07-19
Date time	8	Shown in YYYY-MM-DD HH:MM:SS format, for example: 2009-07-19 11:22:30
TimeStamp	4	displayed in YYYY-MM-DD format, for example: 2009-07-19
Time	3	displayed in HH:MM:SS format. Example: 11:22:30
Year	1	displayed in YYYY format. For example: 2009
Char (M)	M	Fixed-length string.
VarChar (M)	M	Variable length string, requires m<=255
Binary (M)	M	Binary storage similar to char, characterized by insertion of a fixed length of less than 0
VarBinary (M)	M	Variable-length binary storage similar to varchar, characterized by a fixed length not 0
Tiny Text	max:255	Case insensitive
Text	max:64k	Case insensitive
Medium Text	max:16m	Case insensitive
Long Text	max:4g	Case insensitive
Tinyblob	max:255	Case sensitive
Blob	max:64k	Case sensitive
Mediumblob	max:16m	Case sensitive
Longblob	max:4g	Case sensitive
Enum	1 or 2	Up to 65,535 different enumeration values
Set	Up to 8	Up to 64 different values
Geometry
Point
LineString
Polygon
MultiPoint
Multilinestring
Multipolygon
GeometryCollection

Third, the use of recommendations

1, in the specified data type is generally adopted from the small principle, such as the best to use tiny int can not use int, can be used with the type of float is not double type, which will improve the efficiency of MySQL is very large, especially the large data volume test conditions.

2, do not need to design the data sheet is too complex, functional modules on the distinction may be more convenient for later maintenance, careful appearance of a hodgepodge of data sheets

3. Names of data sheets and fields are also a learning

4, design the data sheet structure before you think about it is your room, perhaps the result will be more reasonable and efficient

5, the final design of the database must be a compromise between efficiency and scalability, biased towards either side is defective

Basic principles for selecting data types

Premise: Use a suitable storage engine.
Selection principle: Depending on the storage engine selected, determine how to select the appropriate data type.
The following selection methods are categorized by storage engine:

• MyISAM data storage engine and data columns: MyISAM data tables, it is best to use fixed-length (CHAR) data columns instead of variable-length (VARCHAR) data columns.

• Memory storage engine and data columns: Memory data tables are currently stored using fixed-length data rows, so there is no relationship between using char or varchar columns. Both are handled as char types.

• InnoDB storage engine and data columns: varchar types are recommended.

For INNODB data tables, the internal row storage format does not differentiate between fixed-length and variable-length columns (all data rows use the head pointer pointing to data column values), so in essence, using fixed-length char columns is not necessarily simpler than using variable-length varchar columns. Thus, the main performance factor is the total amount of storage used by the data rows. Since Char takes up more space on average than varchar, it is better to use varchar to minimize the amount of storage and disk I/O for the rows of data that need to be processed.
Here's a list of fixed-length data columns and variable-length data columns.

Char vs. varchar

Char and varchar types are similar, but they are saved and retrieved in different ways. They are also different in terms of their maximum length and whether trailing spaces are retained. No case conversions are made during the storage or retrieval process.
The following table shows the results of saving various string values to char (4) and varchar (4) columns, explaining the difference between char and varchar:

Value	CHAR (4)	Storage requirements	VARCHAR (4)	Storage requirements
''	' '	4 bytes	''	1 bytes
' AB '	' AB '	4 bytes	' AB '	3 bytes
' ABCD '	' ABCD '	4 bytes	' ABCD '	5 bytes
' Abcdefgh '	' ABCD '	4 bytes	' ABCD '	5 bytes

Note that the value of the last row in the table above applies only when strict mode is not used , and if MySQL is running in strict mode, values that exceed the column length are not saved and an error occurs.
Values retrieved from char (4) and varchar (4) Columns are not always the same because trailing spaces are removed from the Char column when retrieved. The difference is illustrated by the following example:
Mysql> CREATE TABLE VC (v VARCHAR (4), C CHAR (4));
Query OK, 0 rows affected (0.02 sec)
Mysql> INSERT into VC VALUES (' AB ', ' ab ');
Query OK, 1 row Affected (0.00 sec)
Mysql> SELECT CONCAT (V, ' + '), CONCAT (c, ' + ') from VC;
+----------------+----------------+
| CONCAT (V, ' + ') | CONCAT (c, ' + ') |
+----------------+----------------+
| AB + | ab+ |
+----------------+----------------+
1 row in Set (0.00 sec)

Related articles:

MySQL Data type detailed

Correct use of numeric types in MySQL data type

Related videos:

Database MySQL Video tutorial