Environment of this article: Windows 7 system, Dell G3 computer.
In the database, most of the time, the "columns" of the table are called "fields", and each field contains information about a certain topic. Just like in the "Address Book" database, "Name" and "Contact Number" are attributes common to all rows in the table, so these columns are called the "Name" field and "Contact Number" field.
Data type (data_type) refers to the type of data allowed in the system. MySQL data types define the rules for what data can be stored in a column and how that data is stored.
Every field in the database should have an appropriate data type that limits or allows the data stored in that column. For example, if the column stores numbers, the corresponding data type should be numeric.
If you use the wrong data type, it may seriously affect the function and performance of the application. Therefore, when designing the table, you should pay special attention to the data type used in the data column. Changing a column containing data is not a trivial matter, and doing so may result in data loss. Therefore, the correct data type and length must be set for each column when creating the table.
The field types in the database table are as follows:
1, binary type
Binary, Varbinary, Image
2 , String type
| Type | Size | Use |
|---|---|---|
| CHAR | 0-255 bytes | Fixed length string |
| VARCHAR | 0-65535 bytes | Variable length string |
| TINYBLOB | 0-255 bytes | A binary string of no more than 255 characters |
| TINYTEXT | 0-255 bytes | Short text string |
| BLOB | 0-65 535 bytes | Long text data in binary form |
| TEXT | 0-65 535 bytes | Long text data |
| MEDIUMBLOB | 0-16 777 215 bytes | Medium-length text data in binary form |
| MEDIUMTEXT | 0-16 777 215 bytes | Medium length text data |
| LONGBLOB | 0-4 294 967 295 bytes | Maximum binary form Text data |
| LONGTEXT | 0-4 294 967 295 bytes | Very large text data |
3. Unicode data types
Including Nchar, Nvarchar and Ntext
4. Date and time data types
| Type | Size (bytes) |
Range | Format | Use |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | Date value |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | Time value or duration |
| YEAR | 1 | 1901/2155 | YYYY | YEAR VALUE |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | Mixed date and time values |
| TIMESTAMP | 4 |
1970-01-01 00:00:00/2038 The end time is 2147483647 seconds, Beijing time 2038-1-19 11:14:07, January 19, 2038 03:14:07 AM GMT |
YYYYMMDD HHMMSS | Mixed date and time value, timestamp |
5. Numeric data type
| Type | Size | Range (signed) | Range ( Unsigned) | Use |
|---|---|---|---|---|
| 1 byte | (-128, 127) | ( 0, 255) | Small integer value | |
| 2 bytes | (-32 768, 32 767) | (0, 65 535) | Large integer value | |
| 3 bytes | (-8 388 608, 8 388 607) | (0, 16 777 215) | Large integer value | |
| 4 bytes | (-2 147 483 648, 2 147 483 647) | (0, 4 294 967 295) | Large integer value | |
| 8 bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | Maximum integer value | |
| 4 bytes | (-3.402 823 466 E 38,-1.175 494 351 E-38 ), 0, (1.175 494 351 E-38, 3.402 823 466 351 E 38) | 0, (1.175 494 351 E-38, 3.402 823 466 E 38) | Single precision | Floating point value |
| 8 bytes | (-1.797 693 134 862 315 7 E 308，-2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E 308) | 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E 308) | Double precision | Floating point value |
| For DECIMAL(M,D), if M>D , is M 2 otherwise D 2 | Depends on the values of M and D | Depends on the values of M and D | Decimal value |
PHP Chinese website!