Detailed explanation of basic data types in mysql8.0.19

![1036857-20170801181433755-146301178](D:\Notes\mysql\Review\1036857-20170801181433755-146301178.png)1. Numbers:
 Integer: tinyinit int bigint
 Decimal:
 float: Inaccurate when the number of digits is shorter double: Inaccurate when the number of digits is longer 0.000001230123123123
 Saved: 0.000001230000

 decimal: (If you use decimals, it is recommended to use decimal)
 The precise internal principle is to store in string form. 2. String:
 char (10): simple and crude, wastes space, fast access root is stored as root000000
 varchar: accurate, space-saving, slow access speed SQL optimization: when creating a table, put fixed-length types first and variable-length types last. For example, if gender, address or description information is > 255 characters, if it exceeds the limit, store the file path in the database.
 For example, find a file server for pictures and videos, and only store the path or URL in the database.

3. Time type:
 Most commonly used: datetime
 
4. Enumeration types and collection types

Integer type tests are signed by default.mysql> create table t1(money tinyint); #Note that there should be no comma after the last field when creating a table. This is an incorrect way to write it.mysql> create table t1(money tinyint,);

mysql> insert into t1 values(11);
Query OK, 1 row affected (0.28 sec)

mysql> select * from t1;

mysql> insert into t1 values(-10);
Query OK, 1 row affected (0.11 sec)

mysql> select * from t1;

mysql> insert into t1 values(-200);
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> select * from t1;

Setting the data type will have a constrained effect, and can only represent numbers within its own range. The writing method for creating an unsigned numeric type is:
mysql> create table t2(id tinyint unsigned);

Testmysql> create table t3(id float(60,30)); # 60 represents the total number of decimal places + integer placesQuery OK, 0 rows affected (1.70 sec) # 30 represents the maximum number of decimal placesmysql> create table t4(id double(60,30));
Query OK, 0 rows affected (0.88 sec)

mysql> create table t5(id decimal(60,30)); # The reason why decimal can store exact values ​​is that it is stored internally as a string.
Query OK, 0 rows affected (0.96 sec)

mysql> insert into t3 values(1.1111111111111111111111);
Query OK, 1 row affected (0.13 sec)

mysql> insert into t4 values(1.1111111111111111111111);
Query OK, 1 row affected (0.22 sec)

mysql> insert into t5 values(1.1111111111111111111111);
Query OK, 1 row affected (0.09 sec)

mysql> select * from t3;
mysql> select * from t4;
mysql> select * from t5;

1. Create table create table t2(f1 float(5,2),f2 float,f3 double(5,2),f4 double);

float(5,2) retains two decimal places and rounds to double(5,2)
2. Write data		
insert into t2 values(5.2336,5.2336,5.336,5.2336);
3. View the data in the table	
select * from t2;
4. Specify the data to be written: insert into t2(f2,f4) values(5.1783682169875975,5.1783682169875975);

insert into table name (field 1, field 3) values ​​(value 1, value 3);

1. Create table t3 create table t3(f1 float, d1 double, d2 decimal(30,20), d3 decimal);
2. View the table structure desc t3;
3. Write data	
insert into t3 values(5.1783682169875975179,5.1783682169875975179,5.1783682169875975179,5.1783682169875975179);

int--------No length constraint, can represent up to 10 digits float(m,n) m-----Total digits n-----Decimal digits

mysql> create table t6(d1 year ,d2 date,d3 datetime);
Query OK, 0 rows affected (1.75 sec)

mysql> insert into t6 values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.12 sec)

mysql> select * from t6;

Performance comparison between char and varchar:
    Compare char(5) and varchar(5). Suppose I want to store three names: sb, ssb1, ssbb2
    char:
      Advantages: Simple and crude. No matter how long the data is, I will store it according to the specified length, 5 by 5. Three names will be stored like this: sb ssb1 ssbb2, with spaces in between. When retrieving data, 5 by 5 are retrieved. It is simple, crude and fast. Disadvantages: It seems to waste space, and the length of the data we store in the future may be uneven. Varchar:
      The varchar type stores data of indefinite length, which is more concise and saves space. For example, when storing the three names above, it would be similar to this: sbssb1ssbb2, connected. If stored like this, how would you retrieve these three names? Do you know how long it should be to retrieve the first one?　
 
      When varchar stores data, it will add a header in front of each data. This header is 1-2 bytes of data, which refers to the length of the data that follows. 1 byte can represent 2**8=256, and two bytes represent 2**16=65536, which can represent numbers from 0 to 65535. So varchar is stored like this: 1byte+sb+1byte+ssb1+1byte+ssbb2. Therefore, it is more troublesome to store, resulting in slower efficiency than char. It is also slower to retrieve, as it takes the length first and then the data.

      Advantages: It saves some hard disk space. A character of ACSII code can be represented by one byte in length. However, it is not necessarily more economical than char. Take a look at the table provided by the official website to compare the data. When the data you store is exactly the field length you specified, varchar actually takes up more space than char.

Value CHAR(4) Storage Required VARCHAR(4) Storage Required
'' ' ' 4 bytes '' 1 byte
'ab' 'ab ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes
　
             Disadvantages: Slow access speed. For InnoDB data tables, the internal row storage format does not distinguish between fixed-length and variable-length columns (all data rows use a header pointer to the data column value). Therefore, in essence, using a fixed-length CHAR column is not necessarily better than using a variable-length VARCHAR column. Therefore, the main performance factor is the total amount of storage used by the data rows. Because CHAR takes up more space on average than VARCHAR, it is better to use VARCHAR to minimize the total amount of storage and disk I/O for the rows of data that need to be processed.

Suitable for using char:
	Identity card number, mobile phone number, QQ number, username, password, bank card number are suitable for using varchar:
	Comments, Moments, Weibo 1. Create table create table t6(c1 char(1),v1 varchar(1),c2 char(8),v2 varchar(8));

2. Write data insert into t6 values('alexq','alexq','alexq','alexq');
3. Query the data in the table select * from t6;

enum: Single-select behavior ------ enumeration type only allows a single value to be selected from a set of values, not multiple values ​​at a time 1. Create table create table t8(id int, name char(18), gender enum('male','female'));
2. Write data insert into t8 values(1,'alex','unknown'); ---------Unknown cannot be written 3. Query the data in the table select * from t8;
4. Write data insert into t8 values(1,'alex','male');-------------male can be written 5. Query the data in the table select * from t8;
2. Write data insert into t8 values(1,'alex','female');------------female can be written 3. Query the data in the table select * from t8;

set:	 
	Multiple selection behavior can **allow arbitrarily selecting 1 or more elements in the value set for combination**. Injection of out-of-range content will not be allowed, and duplicate values ​​will be automatically removed.
1. Create table create table t9(id int,name char(18),hobby set('smoking','drinking','foot washing','massage','perming'));
2. Write data insert into t9 values ​​(1, 'Taibai', 'Perm, Smoking, Drinking, Massage');
3. Query the data in the table select * from t8;
4. Write data insert into t9 values ​​(1, 'Da Zhuang', 'Wash feet, wash feet, wash feet, massage, massage, play games');
5. Query the data in the table select * from t8;