mysql 数据库 II（数据类型）

1.今日内容

搜索引擎介绍
- innodb
- myisam
- memory
mysql中的数据类型
- 数据 int tinyint float
- 时间 datetime date time
- 字符串 char varchar
- enum set

2.具体内容

搜索引擎介绍

innodb

1.事务（保证数字安全，数据的完整性）
2.row-level locking。行级锁
3.table-level locking。 表级锁
4.foreign keys  外键锁
5.加速查询（树型结构+表结构）


MySql 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。

myisam

1.table-level locking。 表级锁
2.加速查询（树型结构+表结构）


MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。

memory

1.基于hash


在内存中存储所有数据，应用于对非关键数据由快速查找的场景。Memory类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失

mysql中的数据类型

数据 int tinyint float

int示例

#int示例

#创建一个表，一个默认宽度的int，一个指定宽度的int（5）
mysql> create table t1(id1 int ,id2 int(5));
Query OK, 0 rows affected (0.03 sec)

#插入数据1
mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------+
| id1  | id2  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

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

#id2显示了正确的数值，没有受到宽度限制的影响
mysql> select * from t1;
+--------+--------+
| id1    | id2    |
+--------+--------+
|      1 |      1 |
| 111111 | 111111 |
+--------+--------+
2 rows in set (0.00 sec)

mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id1` int(11) DEFAULT NULL,
  `id2` int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> into t1 values (22222222,22222222);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----------+----------+
| id1      | id2      |
+----------+----------+
|        1 |        1 |
|   111111 |   111111 |
| 22222222 | 22222222 |
+----------+----------+
3 rows in set (0.00 sec)

#修改id1字段，给字段添加一个unsigned表示无符号
mysql> alter table t1 modify id1 int unsigned;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id1   | int(10) unsigned | YES  |     | NULL    |       |
| id2   | int(5)           | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#当给id1添加的数据大于2147483647时，可以顺利插入
mysql> insert into t1 values (2147483648,2147483647);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------------+------------+
| id1        | id2        |
+------------+------------+
|          1 |          1 |
|     111111 |     111111 |
|   22222222 |   22222222 |
| 2147483648 | 2147483647 |
+------------+------------+
4 rows in set (0.00 sec)

#当个id2添加的数据大于2147483647时，会报错
mysql> insert into t1 values (2147483648,2147483649);
ERROR 1264 (22003): O

小数示例

#小数示例

mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id1   | float(5,2)   | YES  |     | NULL    |       |
| id2   | double(5,2)  | YES  |     | NULL    |       |
| id3   | decimal(5,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t2 values(1.23,1.23,1.23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
1 row in set (0.00 sec)

mysql> insert into t2 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.01 sec)

#向表中插入1.234，会发现4都被截断了
mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
2 rows in set (0.00 sec)

mysql> insert into t2 values(1.235,1.235,1.235);
Query OK, 1 row affected, 1 warning (0.00 sec)

#遵循四舍五入的规则
mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.24 | 1.24 | 1.24 |
+------+------+------+
3 rows in set (0.00 sec)

#去掉参数约束
mysql> create table t3(id1 float,id2 double,id3 decimal);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t3 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)

#发现decimal默认值是（10，0）的整数
mysql> select * from t3;
+-------+-------+------+
| id1   | id2   | id3  |
+-------+-------+------+
| 1.234 | 1.234 |    1 |
+-------+-------+------+
1 row in set (0.00 sec)

mysql> insert into t3 values(1.2345678,1.2345678678,1.2345678);
Query OK, 1 row affected, 1 warning (0.00 sec)

#当对小数位没有约束的时候，输入超长的小数，发现float和double的区别
mysql> select * from t3;
+---------+--------------+------+
| id1     | id2          | id3  |
+---------+--------------+------+
|   1.234 |        1.234 |    1 |
| 1.23457 | 1.2345678678 |    1 |
+---------+--------------+------+
2 rows in set (0.00 sec)

时间 datetime date time

date ，time，datetime示例

mysql> create table t4(d date,t time,dt datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

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

mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2020-01-07 | 22:12:29 | 2020-01-07 22:12:29 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

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

mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2020-01-07 | 22:12:29 | 2020-01-07 22:12:29 |
| NULL       | NULL     | NULL                |
+------------+----------+---------------------+
2 rows in set (0.00 sec)

timestamp示例

#timestamp示例

mysql> create table t5 (id1 timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t5;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)

#插入数据null，会自动插入当前时间的时间
mysql> insert  values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+---------------------+
| id1                 |
+---------------------+
| 2020-01-07 22:15:26 |
+---------------------+
1 row in set (0.00 sec)

mysql> alter table t5 add id2 timestamp;
ERROR 1067 (42000): Invalid default value for 'id2'

#设置默认时间为当前的时间
mysql> alter table t5 add id2 timestamp default current_timestamp;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t5;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2020-01-07 22:15:26 | 2020-01-07 22:21:41 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> show create table t5;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t5    | CREATE TABLE `t5` (
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> inser t5 values (null,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2020-01-07 22:15:26 | 2020-01-07 22:21:41 |
| 2020-01-07 22:23:04 | 2020-01-07 22:23:04 |
+---------------------+---------------------+
2 rows in set (0.00 sec)


mysql> insert into t5 values (19700101080001);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
  
mysql> insert into t5 values (19700101080001,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t5;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2020-01-07 22:15:26 | 2020-01-07 22:21:41 |
| 2020-01-07 22:23:04 | 2020-01-07 22:23:04 |
| 1970-01-01 08:00:01 | 2020-01-07 22:25:39 |
+---------------------+---------------------+
3 rows in set (0.00 sec)

#timestamp时间下限是19700101080001
mysql> insert into t5 values (19700101080000,null);
ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 'id1' at row 1
    
mysql> insert into t5 values('2038-01-19 11:14:07',null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2020-01-07 22:15:26 | 2020-01-07 22:21:41 |
| 2020-01-07 22:23:04 | 2020-01-07 22:23:04 |
| 1970-01-01 08:00:01 | 2020-01-07 22:25:39 |
| 2038-01-19 11:14:07 | 2020-01-07 22:27:09 |
+---------------------+---------------------+
4 rows in set (0.00 sec)

#timestamp时间的上限是2038-01-19 11：14：07
mysql> insert into t5 values('2038-01-19 11:14:08',null);
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 'id1' at row 1

datetime示例

#datetime示例

mysql> create table t6 (dt datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t6;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t6 values('2019-9-2 12:20:11');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t6 values('2018/9/2 12:20:11');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t6 values ('20180902122011');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
+---------------------+
| dt                  |
+---------------------+
| 2019-09-02 12:20:11 |
| 2018-09-02 12:20:11 |
| 2018-09-02 12:20:11 |
+---------------------+
3 rows in set (0.00 sec)

字符串 char varchar

char varchar示例

mysql> create table t7 (v varchar(4),c char(4));
Query OK, 0 rows affected (0.01 sec)

mysql> desc t7;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| v     | varchar(4) | YES  |     | NULL    |       |
| c     | char(4)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into t7 values ('ab  ','ab  ');
Query OK, 1 row affected (0.00 sec)

#在检索的时候char数据类型会去掉空格
mysql> select * from t7 ;
+------+------+
| v    | c    |
+------+------+
| ab   | ab   |
+------+------+
1 row in set (0.00 sec)

#查询结果计算的长度
mysql> select length(v),length(c) from t7;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+
1 row in set (0.02 sec)

#给结构拼上一个加号
mysql> select concat(v,'+'),concat(c,'+') from t7;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab  +         | ab+           |
+---------------+---------------+
1 row in set (0.00 sec)

#当存储的长度超出定义的长度，会截断
mysql> insert into t7 values ('abcd   ','abcd   ');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select concat(v,'+'),concat(c,'+') from t7;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab  +         | ab+           |
| abcd+         | abcd+         |
+---------------+---------------+
2 rows in set (0.00 sec)

enum set

enum set示例

mysql> create table t8(name char(20),gender enum('female','male'));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t8;
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| name   | char(20)              | YES  |     | NULL    |       |
| gender | enum('female','male') | YES  |     | NULL    |       |
+--------+-----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 选择enum('female','male')中的一项作为gender的值，可以正常插入
mysql> insert into t8 values ('chen','male');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t8;
+------+--------+
| name | gender |
+------+--------+
| chen | male   |
+------+--------+
1 row in set (0.00 sec)

# 不能同时插入('female','male')两个值，也不能插入不属于('female','male')的值
mysql> insert into t8 values ('chen','male,female');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> create table t9 (name char(20),hobby set('看书','打乒乓球','玩游戏','旅游'));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t9;
+-------+---------------------------------------------------+------+-----+---------+-------+
| Field | Type                                              | Null | Key | Default | Extra |
+-------+---------------------------------------------------+------+-----+---------+-------+
| name  | char(20)                                          | YES  |     | NULL    |       |
| hobby | set('看书','打乒乓球','玩游戏','旅游')            | YES  |     | NULL    |       |
+-------+---------------------------------------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

# 可以任意选择set('看书','打乒乓球','玩游戏','旅游')中的项，并带去重功能
mysql> insert into t9 values ('chen','看书,玩游戏')；
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '；' at line 1
mysql> insert into t9 values ('chen','看书,玩游戏');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+------+------------------+
| name | hobby            |
+------+------------------+
| chen | 看书,玩游戏      |
+------+------------------+
1 row in set (0.00 sec)
mysql> insert into t9 values ('chen','看书,看书,看书,旅游');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t9;
+------+------------------+
| name | hobby            |
+------+------------------+
| chen | 看书,玩游戏      |
| chen | 看书,旅游        |
+------+------------------+
2 rows in set (0.00 sec)

# 不能选择不属于set('看书','打乒乓球','玩游戏','旅游')中的项
mysql> insert into t9 values('chen','打游戏,听歌');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1