Data types supported by mysql
Integer
''' Type tinyint: 1 byte, value range -128~127, default length 4 smallint: 2 bytes, value range -32768 ~ 32767, default length 6 mediumint: 3 bytes int: 4 bytes -2147483648~2147483647 bigint: 8 words Section'''''' Constraint unsigned: unsigned zerofill: 0 fill'''# Create table mysql>: create table tb1(x tinyint, y smallint, z int(6));# Insert data mysql>: insert into tb1 values(128, 32768, 32768); # Result: 127, 32767, 32768# Conclusion: The length of the integer is determined by the occupied bytes (value range), 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 no conclusions are written>: create table tb1(x tinyint, y smallint, z int);# Integer constraints 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
float type
# test the floating point type in safe mode''' type float(M, D ): 4 bytes, 3.4E–38～3.4E+38 double(M, D): 8 bytes, 1.7E–308～1.7E+308decimal(M, D): based on the large value of the byte M and D +2, in fact, is the value of M +2 is the number of bytes occupied by the decimal field ``'''' width: limit the storage width (M, D) => M is the number of digits, D is the decimal place, and M must be greater than or equal to Dfloat (255, 30): the lowest precision, the most commonly used double (255, 30): high precision, more occupancy d ecimal(65, 30): string storage, full precision'''# Build a table: 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(65, 30)); # In a reasonable range of values mysql>: create table t12 (x float(255, 30)); mysql>: create table t13 (x double(255, 30)); mysql>: create table t14 (x decimal(65, 30));# 1.111111164093017600000000000000 mysql>: insert into t12 values(1.11111111111111111119); # 1.111111111111111200000000000000mysql>: insert into t13 values(1.11111111111111111119);# 1.111111111111111111190000000000mysql>: insert into t14 values(1.11111111111111111119);# Important Analysis # Error, the total length M must be greater than or equal to the 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, and 0.9994 can be stored. That is, 0.999, 0.9995 cannot be stored in mysql>: create table t14 (x decimal(3, 3)); # Integer digits 3-3, so the maximum is 0# Can store -9.999 ~ 9.999, only extra-length decimal places will be available Round up, 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
characters String: database optimization-char efficiency is higher than varchar
"'type char: fixed length, always use the set length to store data varchar: variable length, within the set length range , Variable-length storage data """ width limit storage width char(4): "a" "ab" "abc" "abcd" are stored in 4 lengths, "abcde" can only store the first 4 digits ( Error in safe mode) varchar(4): store "a" "ab" "abc" "abcd" in 1, 2, 3, 4 lengths respectively, "abcde" can only store the first 4 digits (error in safe mode) )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 by a fixed and fixed length, and the efficiency is high. 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 is generally more space-saving, but the calculation is time-consuming, so the data length information calculated by varchar is inefficient, and it needs to open up space for storage. Stored in the data header (before the data starts), it also needs to consume 1~2 extra Bytes, so if the data is of fixed length or fluctuates in a small range, char will not take up more space than char, and the efficiency is high'''# Build a table: mysql>: create table ts1 (s1 char(4), s2 varchar(4));mysql>: insert into ts1 values('adcde','xyzabc'); #'adcd','xyza'
time
< pre class="mysql">”’Type year: yyyy (1901/2155) date: yyyy-MM-dd (1000-01-01/9999-12-31) time: HH:mm:ssdatetime: yyyy-MM -dd HH:mm:ss(1000-01-01 00:00:00/9999-12-31 23:59:59)timesta mp: 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’); # The 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 will have the current system time without copying # datetime: 8 bytes, can be null # timestamp: 4 bytes, There is a default value CURRENT_TIMESTAMP
enumeration and collection
# enumeration and collection: provide options for a certain field-pieces For example, only single selection (1), and multiple selections (0-n) for collections (0-n)# 建表# The default value of enum, set is NULL mysql>: create table tc1 (name varchar(20), sex enum('男', '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('ruakei','Wow Wow','Unknown'); my sql>: insert into tc2(name) values('ruakei'); # Incorrect assignment to sex and hobbies fields, the system fills with empty strings by default (non-safe mode), safe mode throws an exception # If sex, Assign values to the fields other than the two fields of hobbies, these two fields will have default values# Note: Assign a value to the field of the set type, use a string, which is used inside the string, separate multiple options, and cannot Add spaces and other extra characters mysql>: insert into tc2 values('ruakei_1','female','male, female, wow');