1
2
3
4
5
6
7
8

mysql root@localhost:db1> create table table_int(
                              int_no int unsigned,
                              biging_no bigint,
                              tinyint_no tinyint
                          ) default charset=utf8;
                          
Query OK, 0 rows affected
Time: 0.028s

1
2
3
4
5
6
7
8

-- 创建库
create database db1 default charset utf8 collate utf8_general_ci;

-- 创建表
create table L2 ( 
  id int not null auto_increment primary key, 
  salary decimal(8,2) 
) default charset =utf8;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

mysql root@localhost:db1> insert into `L2` (salary) values (1.28);
Query OK, 1 row affected
Time: 0.004s

-- 舍去
mysql root@localhost:db1> insert into `L2` (salary) values( 2.33333);
Query OK, 1 row affected
Time: 0.004s

-- 进位
mysql root@localhost:db1> insert into `L2` (salary) values(6.66666);
Query OK, 1 row affected
Time: 0.004s

-- 查询
mysql root@localhost:db1> select * from `L2`;
+----+--------+
| id | salary |
+----+--------+
| 1  | 1.28   |
| 2  | 2.33   |  -- 2.3333, 2位小数后的值被舍去了。
| 3  | 6.67   |  -- 6.6666, 2为小数后的值被进位了。
+----+--------+
3 rows in set
Time: 0.010s

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 超长， 总长超长
mysql root@localhost:db1> insert into `L2` (salary) values(1234567.890);
(1264, "Out of range value for column 'salary' at row 1")

-- 超长， 正数超长
mysql root@localhost:db1> insert into `L2` (salary) values(1234567);;
(1264, "Out of range value for column 'salary' at row 1")

-- 正确, 小数补齐
mysql root@localhost:db1> insert into `L2` (salary) values(123456);;
Query OK, 1 row affected
Time: 0.004s

-- 正确
mysql root@localhost:db1> insert into `L2` (salary) values(123456.78);;
Query OK, 1 row affected
Time: 0.004s

mysql root@localhost:db1> select * from `L2`;
+----+-----------+
| id | salary    |
+----+-----------+
| 4  | 123456.00 |  -- 小数补齐
| 5  | 123456.78 |
+----+-----------+
5 rows in set
Time: 0.010s

1
2
3
4

mysql root@localhost:db1> create table table_char (
                              char_string char(10) ,
                              varchar_string varchar(10)
                          ) default charset=utf8;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- 12个中文字符超长
mysql root@localhost:db1> insert into table_char(varchar_string) values ("无论插入数据是否为最大");
(1406, "Data too long for column 'varchar_string' at row 1")

-- 14个英文字符超长
mysql root@localhost:db1> insert into table_char(varchar_string) values ("12345678901234");
(1406, "Data too long for column 'varchar_string' at row 1")


-- 10个 中/英文 字符刚好
mysql root@localhost:db1> insert into table_char(varchar_string) values ("无论插入数据是否为最");
Query OK, 1 row affected
Time: 0.004s

mysql root@localhost:db1> insert into table_char(varchar_string) values ("1234567890");
Query OK, 1 row affected

1
2
3
4
5
6
7
8

mysql root@localhost:db1> create table table_text(
                              col_text text,
                              col_medium mediumtext,
                              col_long longtext
                          ) default charset=utf8;

Query OK, 0 rows affected
Time: 0.028s

1
2
3
4
5

mysql> create table table_time (
         ts timestamp,
         dt datetime
     	) default charset=utf8;
Query OK, 0 rows affected (0.02 sec)