Database system principle and Application Tutorial （022）—— MySQL Summary of supported data types

When creating a data table , You need to specify the appropriate data type for the columns in the table .MySQL Support for multiple types , It can be roughly divided into three categories ： The number 、 date / Time and string type .

One 、 Numerical data

Numeric types are divided into integer types 、 Fixed point number type 、 Floating point type .

1、 integer

Integers include TINYINT、SMALLINT、MEDIUMINT、 INT、 BIGINT Other types . The specific usage is shown in the table below ：

explain ：

（1） have access to unsigned Modifier defines unsigned integer , Such as tinyint unsigned The value range of is (0~256).

（2）int(m) Medium m Indicates the display width in the query result , Does not affect the actual value range .

for example ：

mysql> create table t12(id int(15) primary key,name char(20));
Query OK, 0 rows affected (0.25 sec)

mysql> insert into t12 values(1001,'Jack'),(200101,'Mark'),(302321411,'Tom');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t12;
+-----------+------+
| id        | name |
+-----------+------+
|      1001 | Jack |
|    200101 | Mark |
| 302321411 | Tom  |
+-----------+------+
3 rows in set (0.00 sec)

--  Definition  int(5)  Does not affect  int  Value range of .
mysql> create table t11(id int(5) primary key,name char(20));
Query OK, 0 rows affected (0.24 sec)

mysql> insert into t11 values(1001,'Jack'),(200101,'Mark'),(302321411,'Tom');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t11;
+-----------+------+
| id        | name |
+-----------+------+
|      1001 | Jack |
|    200101 | Mark |
| 302321411 | Tom  |
+-----------+------+
3 rows in set (0.00 sec)

2、 Floating point type

Floating point types include float and double Two types of , The specific use method is shown in the table below ：

explain ： If you define a field as float(7,2), Indicates that this field is reserved 2 Decimal place , The total length of 7 position , That is, the maximum integer is 5 position .

（1） If you insert a number 123.45678, What's in the actual database is 123.46;

（2） If you insert a number 12.123456, What's stored is 12.12, If you insert 1200, What's stored is 1200.00.

（3） If the part exceeds the whole number 5 position , Then something goes wrong .

for example ：

mysql> create table t21(id int primary key, name char(20), salary float(7,2));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t21 values(1,' Zhang Peng ',5800);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t21;
+----+--------+---------+
| id | name   | salary  |
+----+--------+---------+
|  1 |  Zhang Peng    | 5800.00 |     --  The decimal part is automatically filled  0
+----+--------+---------+
1 row in set (0.00 sec)

mysql> insert into t21 values(2,' Li Jun ',5628.258);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t21;
+----+--------+---------+
| id | name   | salary  |
+----+--------+---------+
|  1 |  Zhang Peng    | 5800.00 |
|  2 |  Li Jun    | 5628.26 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql> insert into t21 values(3,' Zhang Jing ',105628.258);  --  error （ Integers  6  position ）
ERROR 1264 (22003): Out of range value for column 'salary' at row 1

3、 Fixed point number type

Floating point types store approximate values in the database , The fixed-point type stores the exact value in the database . The fixed point number type is decimal(m,d),m and d The meaning of is exactly the same as the floating-point number type . The parameter m<65,d<30.

for example ：

mysql> create table t22(id int primary key, name char(20), salary decimal(7,2));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t22 values(1,' Zhang Peng ',5800);
Query OK, 1 row affected (0.24 sec)

mysql> insert into t22 values(2,' Li Jun ',5628.258);
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into t22 values(3,' Zhang Jing ',105628.258);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1

mysql> select * from t22;
+----+--------+---------+
| id | name   | salary  |
+----+--------+---------+
|  1 |  Zhang Peng    | 5800.00 |
|  2 |  Li Jun    | 5628.26 |
+----+--------+---------+
2 rows in set (0.00 sec)

Two 、 Character type

String types include ：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 、SET Other types .

1、CHAR and VARCHAR type

explain ：

（1） The grammar format is ： CHAR(n), VARCHAR(n);

（2）CHAR and VARCHAR Type needs to specify length , Length indicates the maximum number of characters to save . for example ： CHAR(20) Indicates that you can save at most 20 Characters .

（3）CHAR The length of the type can be 0 To 255 Any value between . When saving CHAR Value of type , If the number of characters is less than the specified length , Then fill in the space on the right to reach the specified length . When searching CHAR Value of type , Trailing space removed . therefore , We're storing char Value of type , There must be no spaces at the end of the string , Even if there is , It will also be deleted after the query .

（4）VARCHAR The value in the column is a variable length string , Length can be specified as 0 To 65535 Between the value of the . And CHAR Type comparison , VARCHAR When saving the value of type, only the required number of characters is saved , Add another byte to record the length （ If the length of the column declaration exceeds 255, Then use two bytes ）. VARCHAR If the number of characters to be stored is less than the specified length , Do not fill in spaces at the end . The space at the end of the search remains .

（5） If the length of the string to be saved exceeds CHAR or VARCHAR Column specified length , There will be errors .

（6）char String retrieval speed of type is faster than varchar Type fast .

for example ：

mysql> create table t41(id int primary key, name char(3), gender char(1));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t41 values(1,'Tom','M');
Query OK, 1 row affected (0.04 sec)

mysql> select * from t41;
+----+------+--------+
| id | name | gender |
+----+------+--------+
|  1 | Tom  | M      |
+----+------+--------+
1 row in set (0.00 sec)

mysql> insert into t41 values(1,'Jack','M'); --  exceed  3  Characters 
ERROR 1406 (22001): Data too long for column 'name' at row 1

mysql> insert into t41 values(2,' Zhang Jingjing ',' Woman ');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t41;
+----+-----------+--------+
| id | name      | gender |
+----+-----------+--------+
|  1 | Tom       | M      |
|  2 |  Zhang Jingjing     |  Woman      |
+----+-----------+--------+
2 rows in set (0.00 sec)

mysql> insert into t41 values(3,' Zhuge Kongming ',' male ');  --  exceed  3  Characters 
ERROR 1406 (22001): Data too long for column 'name' at row 1

test char and varchar Whether to automatically add spaces ：

mysql> create table t42(id int primary key, name char(10), dept_name varchar(200));
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t42 values(1, ' Zhang Jingjing  ', ' Finance Department ');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t42 values(2, ' Liu tao  ', ' Finance Department  ');
Query OK, 1 row affected (0.04 sec)

mysql> select id, concat('Name:',name,'11') name,
       concat('dept_name:',dept_name,'11') 
       from t42;
-- char  Type will automatically add a space at the end when inserting data , But the trailing space will be deleted when displaying 
-- varchar  Type does not automatically add a space at the end when inserting data , Do not delete spaces when displaying 
+----+------------------+-------------------------------------+
| id | name             | concat('dept_name:',dept_name,'11') |
+----+------------------+-------------------------------------+
|  1 | Name: Zhang Jingjing 11    | dept_name: Finance Department 11                  |
|  2 | Name: Liu tao 11      | dept_name: Finance Department   11                |
+----+------------------+-------------------------------------+
2 rows in set (0.00 sec)

2、BINARY and VARBINARY type

BINARY and VARBINARY The type is similar to CHAR and VARCHAR type . The difference is , They store binary strings , So there is no character set . When saving BINARY Data value , Fill in the tail 0x00（ zero ） Value to reach the specified length , The last byte is not deleted when the value is taken . about VARBINARY, Insert without filling characters , Do not crop bytes when selecting .

BINARY and VARBINARY The length of the type is the number of bytes , Not the number of characters . A western character takes up one byte , The number of bytes occupied by Chinese characters is related to the character set . have access to show character set The number of bytes used by the command to query Chinese characters in each character set ：

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.12 sec)

--  Character set  utf8  Each Chinese character occupies  3  Bytes .

for example ：

mysql> create table t51(id int primary key,name binary(12),addr varbinary(20));
Query OK, 0 rows affected (0.89 sec)

mysql> show create table t51\G
*************************** 1. row ***************************
       Table: t51
Create Table: CREATE TABLE `t51` (
  `id` int(11) NOT NULL,
  `name` binary(12) DEFAULT NULL,
  `addr` varbinary(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
--  surface  t51  The character set used is  utf8, Then a Chinese character occupies  3  Bytes .name  Columns can be saved at most  4  The Chinese characters ,addr  Columns can be saved at most  6  The Chinese characters .

mysql> insert into t51 values(1, ' Zhang Jingjing ', ' Finance Department ');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t51;
+----+--------------+-----------+
| id | name         | addr      |
+----+--------------+-----------+
|  1 |  Zhang Jingjing        |  Finance Department     |
+----+--------------+-----------+
1 row in set (0.03 sec)

mysql> insert into t51 values(2, ' Zhang Jingjing 2', ' Finance Department ');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t51 values(3, ' Liu tao  ', ' Finance Department  ');
Query OK, 1 row affected (0.01 sec)

--  Insert the failure ,name  The column exceeds the specified length 
mysql> insert into t51 values(4, ' Zhuge Kongming 1', ' Finance Department  ');
ERROR 1406 (22001): Data too long for column 'name' at row 1

mysql> select * from t51;
+----+--------------+-------------+
| id | name         | addr        |
+----+--------------+-------------+
|  1 |  Zhang Jingjing        |  Finance Department       |
|  2 |  Zhang Jingjing 2      |  Finance Department       |
|  3 |  Liu tao          |  Finance Department       |
+----+--------------+-------------+
3 rows in set (0.05 sec)

mysql> select id,concat('name:',name,'11') name,concat('addr:',addr,'11') addr from t51;
+----+---------------------+--------------------+
| id | name                | addr               |
+----+---------------------+--------------------+
|  1 | name: Zhang Jingjing    11    | addr: Finance Department 11      |  -- Behind Zhang Jingjing 3 A space 
|  2 | name: Zhang Jingjing 2  11    | addr: Finance Department 11      |  -- Zhang Jingjing 2 In the back 2 A space 
|  3 | name: Liu tao       11   | addr: Finance Department   11    |  -- Behind Liu Tao 6 A space 
+----+---------------------+--------------------+
3 rows in set (0.00 sec)

3、BLOB and TEXT type

（1）BLOB Is a binary large object , Can hold a variable amount of data . Yes 4 Kind of BLOB type ：TINYBLOB、BLOB、MEDIUMBLOB and LONGBLOB. The usage of these four types is exactly the same , The difference is only in the maximum length of the admissible value .

（2）TEXT Type a 4 Kind of ：TINYTEXT、TEXT、MEDIUMTEXT and LONGTEXT.

（3）BLOB Columns can be treated as binary strings ,TEXT Columns can be thought of as character strings , similar CHAR and BINARY. Can be BLOB Columns are considered large enough VARBINARY Column .

（4） Can be TEXT Columns are considered large enough VARCHAR Column .

for example ：

mysql> create table t61(id int primary key, name char(4), memo blob);
Query OK, 0 rows affected (0.57 sec)

mysql> insert into t61 values(1, ' Zhuge Kongming ', ' A wise man ！ ');
Query OK, 1 row affected (0.24 sec)

mysql> select * from t61;
+----+--------------+-------------------------+
| id | name         | memo                    |
+----+--------------+-------------------------+
|  1 |  Zhuge Kongming      |  A wise man ！          |
+----+--------------+-------------------------+
1 row in set (0.00 sec)

mysql> select id,name,concat(memo,'11') memo from t61;
+----+--------------+---------------------------+
| id | name         | memo                      |
+----+--------------+---------------------------+
|  1 |  Zhuge Kongming      |  A wise man ！  11        |
+----+--------------+---------------------------+
1 row in set (0.00 sec)

mysql> create table t62(id int primary key, name char(4), memo text);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t62 values(1, ' Zhuge Kongming ', ' A wise man ！');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t62;
+----+--------------+-----------------------+
| id | name         | memo                  |
+----+--------------+-----------------------+
|  1 |  Zhuge Kongming      |  A wise man ！        |
+----+--------------+-----------------------+
1 row in set (0.00 sec)

mysql> select id,name,concat(memo,'11') memo from t62;
+----+--------------+-------------------------+
| id | name         | memo                    |
+----+--------------+-------------------------+
|  1 |  Zhuge Kongming      |  A wise man ！11        |
+----+--------------+-------------------------+
1 row in set (0.00 sec)

4、ENUM（ enumeration ） and SET（ aggregate ） type

（1）ENUM（ enumeration ） type ：

explain ：

① MySQL Medium ENUM（ enumeration ） Type is a string object , Its value comes from the value explicitly enumerated in the column specification when the table is created .

② When inserting data, only one option can be selected from the enumeration list to insert , You cannot insert multiple options at the same time , This is the SET The biggest difference between types .

③ You can insert NULL.

④ ENUM（ enumeration ） The indexing rules of type are as follows ： For each enumeration value, from 1 Start numbering in sequence , ENUM Values are sorted by index number . for example ： about ENUM(‘a’,‘b’),'a’ be ranked at ’b’ front , But for the ENUM(‘b’,‘a’),'b’ be ranked at ’a’ front .

for example ：

/* create table t71( id int primary key, name char(20), gender enum(' male ',' Woman '), dept_name enum(' The sales department ',' Finance Department ',' Production department ',' Public relations ',' The personnel department ') ); */
mysql> create table t71(
    ->     id int primary key,
    ->     name char(20),
    ->     gender enum(' male ',' Woman '), 
    ->     dept_name enum(' The sales department ',' Finance Department ',' Production department ',' Public relations ',' The personnel department ')
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> insert into t71 values(1,' zhang ',' male ',' Finance Department ');
Query OK, 1 row affected (0.04 sec)

mysql> insert into t71 values(2,' Zhang Jingjing ',' Woman ',' The sales department ');
Query OK, 1 row affected (0.10 sec)

--  Enum types can insert null values 
mysql> insert into t71 values(3,' Zhang Penghui ',null,' The sales department ');
Query OK, 1 row affected (0.01 sec)

--  Insert the failure 
mysql> insert into t71 values(4,' Zhou Yu ',' male ',' The sales department 1');
ERROR 1265 (01000): Data truncated for column 'dept_name' at row 1

mysql> insert into t71 values(5,' Zhang Xueyou ',' male ',' Finance Department '),(6,' Zhou Huajian ',' male ',' The sales department '),(7,' Chow yun-fat ',' male ',' Production department '),    
-> (8,' Zhao Zilong ',' Woman ',' The personnel department '),(9,' Jay Chou ',' male ',' Public relations '),(10,' Publicize ',' Woman ',' The personnel department ');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

--  Press  dept_name  Column sorting 
mysql> select * from t71 order by dept_name;
+----+-----------+--------+-----------+
| id | name      | gender | dept_name |
+----+-----------+--------+-----------+
|  2 |  Zhang Jingjing     |  Woman      |  The sales department     |
|  3 |  Zhang Penghui     | NULL   |  The sales department     |
|  6 |  Zhou Huajian     |  male      |  The sales department     |
|  1 |  zhang       |  male      |  Finance Department     |
|  5 |  Zhang Xueyou     |  male      |  Finance Department     |
|  7 |  Chow yun-fat     |  male      |  Production department     |
|  9 |  Jay Chou     |  male      |  Public relations     |
|  8 |  Zhao Zilong     |  Woman      |  The personnel department     |
| 10 |  Publicize       |  Woman      |  The personnel department     |
+----+-----------+--------+-----------+
9 rows in set (0.05 sec)

（2）SET（ aggregate ） type

explain ：

① SET Syntax of type definition and ENUM The type is exactly the same . The only difference in use is when inserting data ENUM Type can only select one option to insert , and SET Type can insert multiple options .

② SET Is a string object , Can have zero or more values , Its value comes from the options set in the column specification when creating the table .

③ SET The type can be set at most 64 It's worth .

④ For multiple SET The value of the element , When inserting values, the order in which the elements are listed is not important .

for example ：

/* create table user_permission( id int UNSIGNED not null auto_increment primary key, user_id int not null, permission set(' read ',' Comment on ',' Post ') not null, unique (user_id) ); */

mysql> create table user_permission(
    ->     id int UNSIGNED not null auto_increment primary key,
    ->     user_id int not null,
    ->     permission set(' read ',' Comment on ',' Post ') not null,
    ->     unique (user_id)
    -> );
Query OK, 0 rows affected (0.22 sec)

mysql> insert into user_permission(user_id,permission) values (1,' read '),(2,' read '),(3,' read , Comment on '),(4,' reading   read , Comment on , Post ');Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from user_permission;
+----+---------+----------------------+
| id | user_id | permission           |
+----+---------+----------------------+
|  1 |       1 |  read                  |
|  2 |       2 |  read                  |
|  3 |       3 |  read , Comment on             |
|  4 |       4 |  read , Comment on , Post        |
+----+---------+----------------------+
4 rows in set (0.00 sec)

SET（ aggregate ） Type of query ： The number of the first option in the set is 1, The second is 2, The third is 4, The fourth is 8, And so on . Therefore, when querying columns of set type, you can use the number of the set to construct conditions . For example, for the above table user_permission, If you need to permission Column to query , You can use the following methods to construct query conditions ：

（1） The condition that the query contains the first item is ：permission = 1

（2） The conditions for querying the first and second items are ：permission = 3（1+2）

（3） The condition that the query contains the second and third items is ：permission = 6（2+4）

（4） The conditions for querying the first and third items are ：permission = 5（1+4）

（5） The conditions for querying the first three items are ：permission = 7（1+2+4）

for example ：

--  Inquire about  permission  Column contains “ read ” The data of 
mysql> select * from user_permission where permission = 1;
+----+---------+------------+
| id | user_id | permission |
+----+---------+------------+
|  1 |       1 |  read        |
|  2 |       2 |  read        |
+----+---------+------------+
2 rows in set (0.24 sec)

--  Inquire about  permission  Column contains “ read ” and “ Comment on ” The data of 
mysql> select * from user_permission where permission = 3;
+----+---------+---------------+
| id | user_id | permission    |
+----+---------+---------------+
|  3 |       3 |  read , Comment on      |
+----+---------+---------------+
1 row in set (0.00 sec)

--  Inquire about  permission  Column contains “ read ”、“ Comment on ” and “ Post ” The data of 
mysql> select * from user_permission where permission = 7;
+----+---------+----------------------+
| id | user_id | permission           |
+----+---------+----------------------+
|  4 |       4 |  read , Comment on , Post        |
+----+---------+----------------------+
1 row in set (0.00 sec)

--  Inquire about  permission  Column contains “ Comment on ” and “ Post ” The data of 
mysql> select * from user_permission where permission = 6;
Empty set (0.00 sec)

3、 ... and 、 Date time type

Date time types include ：Date、DateTime、TimeStamp、Time、Year

explain ： Data of date and time type must be entered in single quotation marks . It must be a legal date and time , Otherwise it will go wrong .

for example ：

mysql> create table t81(id int primary key, name char(20),birth datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t81 values(1,' zhang ','1998-1-23 8:12:50');
Query OK, 1 row affected (0.00 sec)

--  Do not specify time 
mysql> insert into t81 values(2,' Liu Gang ','1995-10-3');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t81;
+----+--------+---------------------+
| id | name   | birth               |
+----+--------+---------------------+
|  1 |  zhang    | 1998-01-23 08:12:50 |
|  2 |  Liu Gang    | 1995-10-03 00:00:00 |
+----+--------+---------------------+
2 rows in set (0.00 sec)

--  Illegal date or time 
mysql> insert into t81 values(3,' Liu Jingjing ','1995-13-3');
ERROR 1292 (22007): Incorrect datetime value: '1995-13-3' for column 'birth' at row 1
mysql> insert into t81 values(3,' Liu Jingjing ','1995-10-3 88:88:88');
ERROR 1292 (22007): Incorrect datetime value: '1995-10-3 88:88:88' for column 'birth' at row 1

You can specify default values for date and time , for example ：

create table t82(id int primary key,name char(20),createtime datetime default now());

mysql> insert into t82(id,name) values(1,'Jack');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t82;
+----+------+---------------------+
| id | name | createtime          |
+----+------+---------------------+
|  1 | Jack | 2022-07-16 12:54:43 |
+----+------+---------------------+
1 row in set (0.01 sec)

Four 、 Basic principles of data type selection

On the premise of meeting the use needs , Adopt the small principle （ That is, try to use data types that take up less storage space ）, For example, it can be used TINYINT It's better not to INT, It works FLOAT I don 't need one of type DOUBLE type , To save storage space and improve operational efficiency , Especially when the amount of data is very large .

in addition ： Pay attention to the characteristics of various data types and use restrictions . Also consider the impact of various storage engines on data types .