MySQL系列之表和数据类型

MySQL之表和数据类型

一. 查看数据库版本

select version();

二. 数据库操作

2.1. 创建数据库

create database if not exists test_db default charset utf8 collate utf8_general_ci;

这条​​SQL​​​的含义是：如果数据库不存在则创建，存在则不创建，创建​​test_db​​​数据库，并设定字符集为​​utf8​​​和字符序为​​utf8_general_ci​​。

2.2. 删除数据库

drop database test_db;

三. 字符集设置

在数据的存储上，​​MySQL​​提供了不同的字符集支持。而在数据的对比操作上，则提供了不同的字符序支持。

• 字符集（​​character set​​）：定义了字符以及字符的编码。
• 排序规则（​​collation​​）：定义了字符的比较规则。

举一个简单的例子：

有四个字符：​​A、B、a、b​​​，这四个字符的编码分别是​​A = 0, B = 1, a = 2, b = 3​​。这里的字符 + 编码就构成了字符集（​​character set​​）

如果要比较两个字符的大小，例如​​A、B​​​，或者​​a、b​​​，最直观的比较方式是采用它们的编码，比如因为​​0 < 1​​​，所以 ​​A < B​​​；另外，对于​​A、a​​​，虽然它们编码不同，但我们觉得大小写字符应该是相等的，也就是说 ​​A == a​​

这上面定义了两条比较规则，这些比较规则的集合就是​​collation​​。

• 同样是大写字符、小写字符，则比较他们的编码大小
• 如果两个字符为大小写关系，则它们相等

3.1. 查看字符集

show (character set ｜ charset) [like 匹配模式]

每种字符集都有对应的默认排序规则。需要注意下Maxlen这个属性是表示每种字符集最多需要几个字节表示一个字符，下面看一下常见的字符集：

show character set where Charset in ('ascii', 'gb2312', 'gbk', 'utf8', 'utf8mb4')

3.2. 查看比较规则

一种字符集对应有多种比较规则。

show collation [like 匹配模式]

可以看到比较规则的属性还是挺多的，这里面挑选utf8mb4作为研究对象

show collation where Charset = 'utf8mb4'

先看一下排序规则的名称的定义规则：​​utf8mb4_xxxx_xx​​，这里大体可以分为三部分：

• 比较规则的名称用关联字符集的名称开头
• 接着第二部分就是比较规则对应的语言
• 最后一部分是比较规则是否区分语言的重音、大小写等情况

后缀	含义	后缀	含义
_ai	不区分重音	_cs	分区大小写
_as	区分重音	_bin	以二进制比较
_ci	不分区大小写

一般情况选择：​​xxxx_gerneral_ci​​就可以，这是一种通用的比较规则。

3.3. 设置字符集

​​MySQL​​可以设置4个级别的字符集：服务器级别、数据库级别、表级别和列级别。

这里如果数据库级别不指定只会使用服务器级别的设置，表级别的不指定就会使用数据库级别的设置，列级别不指定就会使用数据库级别的设置。

• 服务器级别：
  MySQL服务器的配置文件可以指定：

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

查看MySQL中的字符集和排序规则：

show variables like 'character_set_server';  # 查看字符集
show variables like 'collation_server';      # 查看排序规则

• 数据库级别：
  创建数据库的时候指定字符集和比较规则：

create database if not exists test_db_one default charset utf8 collate utf8_general_ci;

创建之后查看字符集和比较规则：

use test_db_one;
show variables like 'character_set_database';  # 查看字符集：utf8mb3(utf8别名)
show variables like 'collation_database';      # 排序规则：utf8_general_ci

修改数据库的字符集和比较规则：

alter database test_db_one default charset gbk collate gbk_chinese_ci;

如果在创建的时候使用默认：​​create database xxx​​，此时就会使用服务器设置默认字符集和比较规则。

• 表级别：
  建表的时候可以指定字符集和比较规则

create table test_tab (
    id integer primary key
) default character set gbk collate gbk_chinese_ci;

建好之后查看一下：

show create table test_tab;

修改表的字符集和比较规则

alter table test_tab character set utf8 collate utf8_general_ci;

• 列级别：
  在创建表的时候可以单独指定每一列的字符集和比较规则

create table test_tab_1 (
    id integer primary key,
    name varchar(20) character set utf8 collate utf8_general_ci,
    ....
)

修改列的字符集和比较规则

alter table test_tab_1 modify name varchar(20) character set ascii collate ascii_general_ci;

这里需要注意如果修改字符集可能会造成错误，例如上面原来name是utf8字符集改成ascii字符集，如果name中存储了中文字符，但是转成ascii字符集就无法表示，造成错误。

注意：字符集和比较规则是相关连的，无论修改字符集还是排序规则都会导致对应的比较规则和字符集发生相应变化。

• 只修改字符集，比较规则会修改为字符集默认的比较规则
• 只修改比较规则，字符集就会修改为和比较规则对应的字符集

3.4. 扩展

这里需要注意下面三个字符集设置：

show variables like 'character_set_%';

变量	含义
character_set_client	客户端来源数据使用的字符集
character_set_connection	连接层字符集
character_set_results	查询结果字符集

设置方法：

set character_set_client = utf8mb4;  
set character_set_results = utf8mb4;   
set character_set_connection = utf8mb4;
# 简单设置,和上面三条效果一样
set names utf8mb4

也可以在配置文件中设置：

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
[mysql]
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4

上述字符集在使用的时候最好是一致的避免使用的MySQL在各种字符集之间的转换。

四. 表操作

关于表的操作上面已经基本介绍了。

4.1. 创建表

create table if not exists test_tab (
    id integer auto_increment primary key,
    name varchar(20) not null,
    sex binary default 0,
    birth date
) engine=InnoDB default character set utf8mb4 collate utf8mb4_unicode_ci;

这里面​​engine​​​可以不指定默认是​​InnoDB​​​，另外如果数据库指定的​​character set​​这里建表的时候也不需要指定。

建好之后查看一下表结构：

desc test_tab;

4.2. 修改表

修改表分为修改表设置和修改属性

alter table test_tab character set utf8 collate utf8_general_ci;  # 修改表设置

alter table test_tab modify name varchar(20) character set ascii collate ascii_general_ci;  # 修改属性
alter table test_tab add column name varchar(20) character set utf8;  # 增加属性
alter table test_tab drop column name;  # 删除属性

4.3. 删除、清空和重命名

删除表

drop table test_tab;

清空表：​​truncate​​​是整体删除，并且不写​​log​​，也不会激活触发器

truncate table test_tab;

表重命名

rename table test_tab to test_aa;

五. 数据类型

5.1. 整型

类型	字节	范围（有符号）	范围（无符号）	备注
tinyint	1	-128,127	0,255	小整数值
smallint	2	-32768,32767	0,65535	大整数值
mediumint	3	-8388608,8388607	0,16777215	大整数值
int	4			大整数值
bigint	8			超大整数值

属性有符号和无符号：

create table if not exists test_tab (
    ......
    id integer,
    age int unsigned default 0,
    ......
)

5.2. 浮点型

类型	字节	备注
float	4	单精度浮点型
double	8	双精度浮点型
decimal		精确小数值

decimal(M,D)：M表示最大位数（精度），范围是1-65，默认值10；D表示小数点右边的位数（小数位），范围是0到30，并且不能小于M，可不指定默认是0。

​​decimal​​​类型是MySQL官方唯一指定能精确存储的类型，推荐和金钱相关的类型都要存储为​​decimal​​类型，否则精度会丢失。

5.3. 字符

类型	字节	备注
char	0-255字节	定长字符串
varchar	0-65535字节	变长字符串
tinytext	0-255字节	短文本字符串
text	0-65535字节	长文本数据
longtext	0-4294967295字节	极长文本数据
tinyblob	0-255字节	二进制字符串
blob	0-65535字节	二进制形式的长文本数据
longblob	0-4294967295字节	二进制形式的极长文本数据

这里面需要注意：

• blob类型主要用于存放二进制大对象，例如存储图片、音频、视频
• char类型是定长的，长度不够的时候会通过右边填充空格以达到指定长度，当检索到char的值时，尾部空格被删除掉
• varchar类型是存储可变长度字符串，存储时，字符没达到定义长度，也不会补空格。

5.4. 日期类型

类型	字节	格式	备注
date	3	yyyy-MM-dd	存储日期值
time	3	HH:mm:ss	存储时分秒
yaer	1	yyyy	存储年
datetime	8	yyyy-MM-dd HH:mm:ss	存储日期+时间
timestamp	4	yyyy-MM-dd HH:mm:ss	存储日期+时间，可作时间戳