MySQL

2022-06-10 03:23:24

MySQL介绍

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

MySQL数据类型

整型

MySQL中整型也有不同的类型。它们分别是tinyint、smallint、mediumint、int、bigint，不同的int类型存储的数字范围是不同的。如图：

MySQL

根据图片我们知道，int类型数字范围有包括正负号和只有整数的，那么MySQL默认整型的取值范围是哪儿个呢？我们直接检验下：

MySQL

结论： 默认MySQL的int类型都是使用正负号范围的。

但是，我们如果想要使用整数范围的int类型，可以给字段约束条件(unsigned)，如下：

mysql> create table t1(id tinyint unsigned);

整型类型括号内数字的作用

我们看以下例子：

mysql> create table foo(id int(3));
mysql> insert into foo values(4444444);

mysql> select * from foo;
+---------+
| id      |
+---------+
| 4444444 |
+---------+
1 row in set (0.00 sec)

我们发现数字正常显示，其实：

在整型中括号内的数字并不是用来限制存储的长度，而是用来控制展示的长度。我们以后在定义整型字段的时候，不需要自己添加数字，使用默认的就可以。

我们给类型加个zerofill（展示的数据长度不够使用0填充）测试下：

create table t1(id int(3) zerofill);
insert into t1 values(4);

mysql> select * from t14;
+------+
| id   |
+------+
|  004 |
+------+

浮点型

浮点型有float、double、decimal，它们范围和精度各不相同。

MySQL

我们实际检验下它们的精确度，

mysql> create table t1(id float(255,30));  # 总共255位,小数位占30位
mysql> create table t2(id double(255,30)); # 总共255位,小数位占30位
mysql> create table t3(id decimal(65,30)); # 总共65位,小数位占30位
mysql> insert into t1 values(1.11111111111111111111111);
mysql> insert into t2 values(1.11111111111111111111111);
mysql> insert into t3 values(1.11111111111111111111111);

插入相同数据到各表中后，查看：

MySQL

结论：精确度从高到低为 decimal > double > float

在实际应用场景中我们根据情况而定选择合适的数据类型，一般保留两位小数float也足够了。

字符类型

字符类型有char、varchar等。看单词的命名多少可以猜出varchar是变化的char，那么char是固定长度的。char和varchar使用时必须使用()并填入数字确定字符类型的长度，我们先简单看看它们如何使用。

mysql> create table t1(id int,name char(4));
mysql> create table t2(id int,name varchar(4));
mysql> insert into t1 values(1,'ii');
mysql> insert into t2 values(1,'ii');
mysql> insert into t1 values(2,'xxxxx');
mysql> insert into t2 values(2,'xxxxx');

MySQL

上述我们为char和varchar设置长度为4个字符，但是我们插入表数据'xxxxx'为5个字符，mysql自动为我们做了截取，这不太合理。也就是说，我们需要在插入数据超出给定范围时报错，那么这该如何设置呢？

1.修改mysql配置文件（my-default.ini或自定义配置文件）

# 默认5.6及以上版本，mysql都已经配置了STRICT_TRANS_TABLES
sql_mode=STRICT_TRANS_TABLES

2.使用命令修改

mysql> show variables like '%mode%'
+----------------------------+------------------------+
| Variable_name              | Value                  |
+----------------------------+------------------------+
| binlogging_impossible_mode | IGNORE_ERROR           |
| block_encryption_mode      | aes-128-ecb            |
| gtid_mode                  | OFF                    |
| innodb_autoinc_lock_mode   | 1                      |
| innodb_strict_mode         | OFF                    |
| pseudo_slave_mode          | OFF                    |
| slave_exec_mode            | STRICT                 |
| sql_mode                   | NO_ENGINE_SUBSTITUTION |
+----------------------------+------------------------+
8 rows in set (0.03 sec)

mysql> set gloabl sql_mode = 'STRICT_TRANS_TABLES'

我们关闭mysql服务再次启动服务，然后重新打开mysql客户端。

mysql> insert into t1 values(3,'hhhhhhh');
ERROR 1406 (22001): Data too long for column 'name' at row 1

此时，插入数据过长就会报错。

char与varchar区别

char为定长类型，超出长度报错，少于给定长度也会补空格填充，再存储到硬盘中。
varchar为变长类型，超出长度报错，它有几个字符就占到多少字符。

我们通过char_length()查看一下上述例子中插入字符ii到两表中的长度，

MySQL

我们通过char_length()得到字符长度都是2，并不像我开头描述的那样，其实这是因为查询时mysql自动为我们去除了空格字符。我们通过设置sql_mode=pad_char_to_full_length来验证我们的想法，设置后退出mysql客户端重新登录。

mysql> set global sql_mode = 'strict_trans_tables,pad_char_to_full_length'

mysql> insert into t1 values(3,'ab');
Query OK, 1 row affected (0.35 sec)

mysql> insert into t2 values(3,'ab');
Query OK, 1 row affected (0.11 sec)

mysql> select char_length(name) from t1 where id=3;
+-------------------+
| char_length(name) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.00 sec)

mysql> select char_length(name) from t2 where id=3;
+-------------------+
| char_length(name) |
+-------------------+
|                 2 |
+-------------------+
1 row in set (0.00 sec)

那么char与varchar各有什么优劣势呢？

char是整存整取，速度快；varchar存储更节省空间，但是存取速度较char慢。因为varchar是可变长类型，所以取数据不能像char一样直接整取，数据长度参差不齐，因此varchar在存储数据时会为每个数据前加1bytes的数据（此数据为接下来要取数据的长度），这样读取数据前需要先读取这1bytes的数据来获得接下来要取的数据的长度，速度自然也就较char慢了。

枚举与集合类型

枚举

数据类型为枚举的字段在插入数据时，只能使用创建表时定义的那些中的一个。

mysql> create table user(
mysql> id int,
mysql> name varchar(32),
mysql> gender enum('male','female','others')
mysql> );
mysql> insert into user values(1,'xie','男');    # 报错
mysql> insert into user values(1,'lin','male');  # 正常

数据类型为集合的字段在插入数据时，可以使用表创建时定义的那些中的多个。

mysql> create table user(
mysql> id int,
mysql> name varchar(32),
mysql> hobby set('basketball','football','yolleyball')
mysql> );
mysql> insert into user values(1,'xie','basketball,football');

日期类型

日期类型有date、datetime、time、yea、timestampr等类型。

类型 格式 示例
DATE YYYY-MM-DD 2022-2-18
TIME hh:mm:ss[.uuuuuu] 12:59:59.123456
DATETIME YYYY-MM-DD hh:mm:ss[.uuuuuu] 2022-2-18 12:59:59.123456
TIMESTAMP YYYY-MM-DD hh:mm:ss[.uuuuuu] 2022-2-18 12:59:59.123456
YEAR YYYY 2022

示例：

mysql> create table client(
mysql> id int,
mysql> name varchar(32),
mysql> reg_time date,
mysql> birth datetime,
mysql> study_time time,
mysql> join_time year
);

mysql> insert into client values(1,'xie','2022-2-18','2022-2-18 12:59:59','12:59:59',2022);

约束条件

约束条件相当于在字段类型的基础上添加额外的约束。

  • UNSIGNED 数字无符号，即0开始

  • ZEROFILL 展示数字长度小于设置的大小用0填充

  • NOT NULL 或 NULL（默认） 非空或允许为空。使用NULL约束条件的字段不传值时默认为NULL，设置为NOT NULL不传值便会报错。

  • DEFAULT 默认值，即插入数据不给该字段传值时的默认值。

  • UNIQUE

    # 单列唯一
# 设置约束条件unique的字段其值只能唯一，不能重复
create table t1(
	id int,
	name varchar(32) unique
);

# 联合唯一(必须放在最后)
# 联合的字段其值的组合只能唯一，不能重复
create table t2(
	id int,
	host varchar(32),
	port int,
	unique(host,port)
);

  • PRIMARY KEY（主键）
    primary key从约束层面上来说，相当于是NOT NULL+UNIQUE。且在此基础上查询速度更快。
    InnoDB存储引擎规定表有且只有一个主键。InnoDB是通过主键来构建表的，如果未设置主键和约束条件，InnoDB会采用隐藏的字段作为主键，但不再加快查询速度。如果未设置主键，但是设置了NOT NULL和UNIQUE的字段将自动升级为主键。

    MySQL

    如上图可知，设置了多个非空且唯一，只将最前的字段升级为主键。
    一般我们把表的id设置成主键。

  • AUTO_INCREMENT
    自增，只能用于数字类型。一般配合主键使用，设置此约束条件的字段在我们插入新行后会自动+1。
    AUTO_INCREMENT设置的字段在我们删除一条数据再插入一条数据后，其自增数不会自减。此处看以下的操作：

    create table foo(
id int primary key auto_increment,
name varchar(32),
age int
);

insert into foo(name,age) values('xie',17),('lin',14),('boy',23),('kavi',34);

select * from foo;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | xie  |   17 |
|  2 | lin  |   14 |
|  3 | boy  |   23 |
|  4 | kavi |   34 |
+----+------+------+

delete from foo where id=4;
insert into foo(name,age) values('gane',20);

 select * from foo;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | xie  |   17 |
|  2 | lin  |   14 |
|  3 | boy  |   23 |
|  5 | gane |   20 |
+----+------+------+

    我们看到直接从5开始，自增数并不会自减哦！并且我们使用delete清空表数据再插入数据，id还是从使用过的最大数字开始自增。如果您想要id重新开始，那么就使用truncate 表名清空表数据并且重置主键值。

上一篇：Python 获取防火墙策略并保存为TXT文档


下一篇：RISC-V CPU加电执行流程