One, data type (important)

mysql The database supports the following data types:

• Integer type| Floating point type| Character type| Time type| Enumeration type| Collection type

Integer

Key points:tinyint, int, constraints (unsigned, zerofill)

Type

tinyint: 1 byte -128~127 (for logical judgment) Key points
smallint: 2 bytes -32768~32767
mediumint:3 bytes
int:4 bytes -2147483648~2147483647 (int length is 11 bits, if it exceeds the length, use string) Key points
bigint: 8 bytes

Constraints (key master*)

unsigned: Unsigned< br> zerofill: 0fill

# Build table mysql>: create table tb1(x tinyint, y smallint, z int (6));# Insert data (error directly in safe mode) mysql>: insert into tb1 values(128, 32768, 32768); # Result: 127, 32767, 32768# Conclusion: The length of the integer is the word occupied The section (value range) is determined, and the length can be customized, but it does not affect the occupied bytes (value range) # The length of all integer variables is generally omitted, and the width is not written. 1. Can not determine the integer storage data Width, it can be stored if it exceeds the width, and it is finally determined by the bytes occupied by the data type. 2. If the width is not exceeded and there is a zerofill limit, the insufficient bits of the preamble will be filled with 0. 3. There is no need to specify the width of the integer, the default setting The width of the integer is the maximum width of the data that the integer can store*'''# Integer constraint (error directly in the safe mode) mysql>: create table tb2(x tinyint unsigned); # 0~255mysql>: insert into tb2 values(256), (-1); # 255, 0 # 0 Fill constraints mysql>: create table tb3(x tinyint unsigned zerofill);mysql>: insert into tb3 values(10); # 010

floating point

Key points:float(255, 30), decimal(65, 30)

type

< code>float(M, D): 4 bytes, 3.4E–38～3.4E+38
double(M, D): 8 bytes, 1.7E–308～1.7E+308
decimal(M, D): Based on the large value of the byte M and D +2, in fact, the value of M +2 is the number of bytes occupied by the decimal field

width

Limit storage width
(M, D) => M is the total number of digits, D is the decimal place, M must be greater than or equal to D

float(255, 30) : The lowest precision, the most commonly used
double(255, 30): High precision, more space
decimal(65, 30): String storage, full precision

# Test floating-point type in safe mode# Table creation: mysql>: create table tb4 (age float(256, 30)); # Display width out of range for column'age' (max = 255)mysql>: create table tb5 (age float(255, 31)) ; # Too big scale 31 specified for column'age'. Maximum is 30.mysql>: create table tb5 (age float(255, 30)); # In a reasonable range mysql>: create table t12 (x float(255 , 30));mysql>: create table t13 (x double(255, 30));mysql>: cre ate table t14 (x decimal(65, 30));mysql>: insert into t12 values(1.11111111111111111119); #The progress is the lowest, the most commonly used# 1.111111164093017600000000000000 mysql>: insert into t13 values(1.11111111111111111119); # 1.111111111111111200000000000000 mysql>: insert into t14 values(1.11111111111111111119); # String storage, full precision# 1.111111111111111111190000000000# Important: Length and decimal places analysis# Error, the total length M must be greater than or equal to decimal places Dmysql>: create table t14 (x decimal (2, 3));# Can store -0.999 ~ 0.999, the decimal places of excessive length will be rounded, 0.9994 can be stored, that is, 0.999, 0.9995 can not be stored mysql>: create table t14 (x decimal(3, 3) ); # The integer digits are 3-3, so the maximum is 0# It can store -9.999 ~ 9.999, and the decimal places of excessive length will be rounded off. 9.994 can be stored, that is, 9.999,9.9995 can not be stored mysql>: create table t14 (x decimal(4, 3)); # The integer digits are 4-3, so the maximum is 9# It can store -99.999 ~ 99.999, and the decimal places of excessive length will be rounded. 99.9994 can be stored, that is, 99.999, 99.9995 cannot be stored in mysql> : create table t14 (x decimal(5, 3)); # The integer digits are 5-3, so the maximum is 99

< strong>Character type: database optimization-char efficiency is higher than varchar

Key point: Use char if you can use char. Database optimization

type

char: fixed length 4 bytes, always use the set length to store data
varchar: variable length, variable length storage data within the set length range

Width

Function : Limit the storage width

Char is stored as a fixed length. If the data length changes greatly, it usually takes up more space, but the access data is operated at a fixed and fixed length, which is efficient

< p>When varchar stores data, it will first calculate the length of the data to be stored, and dynamically change the length to store the data, so it generally saves space, but the calculation takes time, so the efficiency is low

varchar calculated Data length information also needs to open up space for storage, stored in the data header (before the data starts), it also needs to consume an additional 1~2 bytes

So if the data are all fixed length, or small range Fluctuation, char will not take up more space than char, and the efficiency is high

# Create table: mysql>: create table ts1 (s1 char(4), s2 varchar(4)) ;mysql>: insert into ts1 values('adcde','xyzabc'); #'adcd','xyza'

time type

Key points:datetime (8 bytes, can be null), timestamp (4 bytes, with default value CURRENT_TIMESTAMP)

Type

year: yyyy(1901/2155)
date: yyyy-MM-dd(1000-01-01/9999-12 -31)
time: HH:mm:ss
datetime: yyyy-MM-dd HH:mm:ss(1000-01-01 00: 00:00/9999-12-31 23:59:59)
timestamp: yyyy-MM-dd HH:mm:ss(1970-01-01 00:00:00/2038 -01 -19 ??)

# Create table: mysql>: create table td1 (my_year year, my_date date, my_time time);mysql>: insert into td1 values(1666, '8888 -8-8', '8:8:8'); # Time needs to be in the value access mysql>: create table td2 (my_datetime datetime, my_timestamp timestamp); mysql>: insert into td2 values('2040-1- 1 1:1:1', '2040-1-1 1:1:1'); # Time needs to be in the value access mysql>: insert into td2(my_datetime) values('2040-1-1 1:1 :1'); # timestamp does not copy the current time of the system.# datetime: 8 bytes, can be null# timestamp: 4 bytes, with default value CURRENT_TIMESTAMP

enumeration type and collection type

type

enum: Single choice< br> set: multiple selection

# 建表# enum, set default value is NULLmysql>: create table tc1 (name varchar(20), sex enum ('Male','Female','Wow'), hobbies set('Male','Female','Wow'));mysql>: insert into tc1 values('ruakei','Wow wow','Unknown '); # enum, set manually set the default values ​​of'Male' and'Wow' mysql>: create table tc2 (name varchar(20), sex enum('Male','Female','Wow') default'Male' , hobbies set('Male','Female','Wow') default'Wow');mysql>: insert into tc2 values('ru akei','wow wow','unknown'); mysql>: insert into tc2(name) values('ruakei'); # Assignment errors to the two fields of sex and hobbies, the system defaults to filling with empty strings (not safe Mode), safe mode throws exception# If you assign values ​​to other fields except sex and hobbies, these two fields will have default values# Note: To assign a value to a set type field, use a string, character The string is used internally to separate multiple options, and other extra characters such as spaces cannot be added mysql>: insert into tc2 values('ruakei_1','female','male, female, wow');

Contents