Data types are the basic rules that define what data can be stored in a column and how the data is stored
When designing a table, you should pay special attention to the data type used. Using the wrong data type may severely affect the functionality and performance of the application. Changing the column containing data is not a trivial matter (data may be lost)
1 String data type
The most commonly used The data type is the string data type
The data type is the basic rule that defines what data can be stored in the column and how the data is stored
When designing a table, special attention should be paid to the data type used. Using the wrong data type may severely affect the functionality and performance of the application. Changing the column containing data is not a trivial matter (data may be lost)
There are two The basic string types are fixed-length strings and variable-length strings.
Performance, mysql processing fixed-length columns is much faster than processing variable-length columns. In addition, mysql does not allow indexing of variable-length columns (or variable parts of a column), which also affects performance
Fixed-length strings accept fixed-length strings. The length is specified when the table is created.
Fixed-length strings are not allowed to exceed the specified number of characters, and they allocate as much storage space as specified.
CHAR is a fixed-length string
Variable-length string storage Variable-length text. Some variable-length data types have the largest fixed length. And some are completely variable-length
TEXT belongs to variable-length type
Since variable-length data types are so flexible, why use fixed-length data types?
Performance, mysql processing fixed-length columns is much faster than processing variable-length columns. In addition, MySQL does not allow indexing on variable-length columns (or variable parts of a column), which also affects performance
| Data type | Description |
|---|---|
| CHAR | 1-255 characters Fixed-length string, its length must be specified when it is created, otherwise mysql assumes CHAR(1) |
| ENUM | accepts the most A string of a predefined set composed of 64 strings |
| LONGTEXT | Same as TEXT, but the maximum length is 4GB< /td> |
| MEDIUMTEXT | Same as TEXT, but the maximum length is 16K |
| SET | Accepts zero or more strings of a predefined set consisting of up to 64 strings |
| TEXT | Side length text with a maximum length of 64K |
| TINYTEXT | Same as TEXT, the maximum length is 255 bytes |
| VARCHAR | The length is variable, up to 255 bytes. If it is specified as varchar(n) when creating, it can store a side length string of 0 to n characters (n<=255) |
No matter what form of string data type is used, it must be enclosed in quotation marks (usually single quotation marks are better)
Phone numbers and postal codes should be stored in the string data type. (If it is stored in a numeric field with 0 at the beginning, it will actually lose a digit)
2numerical data type
| Data type | Description |
|---|---|
| BIT< /td> | Bit field, 1~64 bits |
| BIGINT | Integer type, supports-9223372036854775808~+9223372036854775807 ( If it is UNSIGNED, it is the number of 0~18446744073709551615) |
| BOOLEAN (or BOOL) | Boolean, either 0 or 1 , Mainly used for on/off |
| DECIMAL | (or DEC |
| DOUBLE | Double precision floating point |
| FLOAT | Single precision floating point Type |
| INT (or INTEFER) | Integer type, support -2147483648~+2147483647 (if UNSIGNED, 0~4294967295 ) |
| MEDIUMINT | Integer type, supports -8388608~8388607 (if UNSIGNED, 0~16777215) |
| REAL | 4-byte floating point value |
| SMA LLINT | Integer value, support -32768~35767 (if UNSIGNED, 0~65535) number |
| TINYINT | Integer type, supports -128 to 127 (if UBSIGNED), which is a number (0~255) |
Numerical value It should not be enclosed in quotation marks.
Except BIT and BOOLEAN, all other numeric data types can be signed or unsigned.
There is no data type specifically storing currency. In general, use DECIMAL (8, 2)
Date and time data type
| Data type | Description |
|---|---|
| DATE | indicates the date, the format is YYYY-MM-DD |
| TIME | The format is HH:MM:ss |
| DATETIME | Combination of DATE and TIME |
| TIMESTAMP | The function is the same as DATETIME, but the scope is smaller |
| YEAR | Expressed by 2 digits, the range is 70 (1970)-69 (2069); Expressed by 4 digits , The range is 1901~2155 |
Binary data type
Binary data type can be stored Any type, such as images, multimedia, word processing documents, etc. |Data type|Description| |-|-| |BLOB|The maximum length of a blob is 64KB| |MEDIUMBLOB| The maximum length of blob is 16MB| |LONGBLOB|The maximum length of blob is 4GB| |TINYBLOB|The maximum length of blob is 255 bytes|