Mysql data type
MySQL stores data. As long as it is data, we will specify the type of data. A certain data type is specified in the fields of the table. Then, the corresponding data type must be used in the inserted data. Also, adhere to the length requirements of the data type.
In MySQL we divide data types into the following types:
- Numeric type (integer, floating point)
- String type
- Date time type
- Composite type
- Space type (basically not used in non-scientific work, no explanation will be given)
Integer type
MySQL data type | Bytes occupied | Value range |
---|---|---|
tinyint | 1 byte | -128~127 |
smallint | 2 byte | -32768~32767 |
mediumint | 3 bytes | -8388608~8388607 |
int | 4 bytes | Range -2147483648~2147483647 |
bigint | 8 bytes | +-9.22*10 of 18 Power |
#The length of the integer is different, and the actual use process is also different.
MySQL extends the SQL standard in the form of an optional display width indicator so that when a value is retrieved from the database, the value can be lengthened to a specified length. For example, specifying that the type of a field is INT(6),
ensures that values containing less than 6 digits are automatically filled with spaces when retrieved from the database. Note that using a width indicator does not affect the size of the field or the range of values it can store.
Note:
- When creating table fields, we can use unsigned tiny integers (tinyint) to represent gender. Use 0 to represent female and 1 to represent male. Use 2 to represent unknown.
- Similar to human age, unsigned integers can be used when creating table fields. Because human age has not yet had a negative number
- in actual use. What is the maximum value that needs to be stored in our business. When we create a table, we choose what type to store such values.
Floating point type
MySQL data type | Bytes occupied | Value range |
---|---|---|
float(m, d) | 4 bytes | Single precision floating point type, m total number, d decimal Bits |
double(m, d) | 8 bytes | Double precision floating point type, m total number, d decimal places |
decimal(m, d) | decimal is a floating point number stored as a string |
Note:
- Floating point is an inexact value, and there may be inaccuracies
- And decimal is called a fixed-point number. Internally, MySQL is essentially stored as a string. In actual use, if there is floating-point number storage with relatively high requirements for amount and money precision, it is recommended to use the decimal (fixed-point number) type.
Character type
MySQL data type | Bytes occupied | Value range |
---|---|---|
CHAR | 0-255 bytes | Fixed length string |
VARCHAR | 0-255 bytes | Variable length string |
TINYBLOB | 0-255 bytes | Binary string not exceeding 255 characters |
TINYTEXT | 0-255 bytes | Short text string |
BLOB | 0-65535 bytes | Long text data in binary form |
TEXT | 0 -65535 bytes | Long text data |
MEDIUMBLOB | 0-16 777 215 bytes | Medium length in binary form Text data |
MEDIUMTEXT | 0-16 777 215 bytes | Medium length text data |
LOGNGBLOB | 0-4 294 967 295 bytes | Very large text data in binary form |
LONGTEXT | 0-4 294 967 295 bytes | Very large text data |
VARBINARY(M) | Allows fixed-length bytes with a length of 0-M bytes String | The length of the value + 1 byte |
BINARY(M) | M | Allowed length 0-M A fixed-length byte string of bytes |
*CHAR * type is used for fixed-length strings and must be defined with a size modifier within parentheses. This size modifier ranges from 0-255. Values larger than the specified length will be truncated, while values smaller than the specified length will be padded with spaces.
*VARCHAR * Treat this size as the size of the value, and fill it with spaces if the length is not insufficient. The VARCHAR type treats it as the maximum value and only uses the length actually needed to store the string
The type is not padded with spaces, but values longer than the indicator are still truncated.
Because the VARCHAR type can dynamically change the length of the stored value based on the actual content, using the VARCHAR type can greatly save disk space and improve storage efficiency when you are not sure how many characters a field requires.
text type and blob typeWhen the field length requirement exceeds 255, MySQL provides two types: TEXT and BLOB. They all have different subtypes based on the size of the stored data. These large data are used to store text blocks or binary data types such as images and sound files.
There are differences in classification and comparison between TEXT and BLOB types. The BLOB type is case-sensitive, while TEXT is not case-sensitive. Size modifiers are not used on various BLOB and TEXT subtypes.
Bytes occupied | Value range | |
---|---|---|
3 bytes | Date, format: 2014-09-18 | |
3 bytes | Time, format: 08:42:30 | |
8 bytes | Date time, format: 2014-09-18 08:42:30 | |
4 bytes | Automatic storage record modification Time | |
1 byte | Year |
- The time type is rarely used in web systems. Many people like to use int to store time. When inserting, the unix timestamp is inserted because this method is more convenient for calculation. Use date type functions in front-end business to convert unix timestamps into time that people can recognize. You can choose the above types according to the actual situationSome people use the datetime type to store time in order to facilitate viewing in database management.
Explanation | Example | |
---|---|---|
Set type | set(“member”, “member2″, … “member64″) | |
Enumeration type | enum(“member1″, “member2″, … “member65535″) |