Reference article:
1. /me19860115/blog/item/
one,MySQLData type
It mainly includes the following five categories:
Integer types: BIT, BOOL, TINY INT, SMALL INT, MEDIUM INT, INT, BIG INT
Float number types: FLOAT, DOUBLE, DECIMAL
String type: CHAR, VARCHAR, TINY TEXT, TEXT, MEDIUM TEXT, LONGTEXT, TINY BLOB, BLOB, MEDIUM BLOB, LONG BLOB
Date type: Date, DateTime, TimeStamp, Time, Year
Other data types: BINARY, VARBINARY, ENUM, SET, Geometry, Point, MultiPoint, LineString, MultiLineString, Polygon, GeometryCollection, etc.
2. Length and range of MYSQL data types
List of data types and byte lengths:
| Data Type | bytelength | Scope or usage |
| Bit | 1 | Unsigned [0,255], signed [-128,127], Note: Both BIT and BOOL booleans occupy 1 byte |
| 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. D here is the accuracy. If D<=24, it is the default FLOAT, and if D>24, it will be automatically converted to DOUBLE type. |
| Double(M,D) | 8 | Double precision floating point. |
| Decimal(M,D) | M+1 or M+2 | Unpacked floating-point numbers are used similarly to FLOAT and DOUBLE. If the Decimal data type is used in ASP, the Decimal read directly from the database may need to be converted to Float or Double type before performing calculations. |
| Date | 3 | Displayed in YYYY-MM-DD format, for example: 2009-07-19 |
| Date Time | 8 | Displayed in the format of YYYY-MM-DD HH:MM:SS, 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. For 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, required M<=255 |
| Binary(M) | M | Similar to Char's binary storage, the feature is that the insertion is not enough to make up for 0 |
| VarBinary(M) | M | VarChar-like variable length binary storage, with the characteristics of fixed length not supplementing 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 sensitivity |
| Blob | Max:64K | Case sensitivity |
| MediumBlob | Max:16M | Case sensitivity |
| LongBlob | Max:4G | Case sensitivity |
| Enum | 1 or 2 | Up to 65535 different enum values |
| Set | Up to 8 | Up to 64 different values |
| Geometry | ||
| Point | ||
| LineString | ||
| Polygon | ||
| MultiPoint | ||
| MultiLineString | ||
| MultiPolygon | ||
| GeometryCollection |
3. Use suggestions
1. When specifying data types, the principle is generally adopted. For example, if you can use TINY INT, you should not use INT, and if you can use FLOAT, you should not use DOUBLE. This will greatly improve the operating efficiency of MYSQL, especially under the conditions of large data testing.
2. There is no need to design the data table too complicated. The distinction between functional modules may be more convenient for later maintenance. Carefully, the hodgepodge data table appears.
3. Naming data tables and fields is also a science
4. Before designing the data table structure, please imagine that it is your room. Perhaps the results will be more reasonable and efficient.
5. The final design result of the database must be a compromise between efficiency and scalability, and it is inappropriate to lean towards either party.