MySQL字段类型与约束条件
创建表的完整语法
创建表完整语法
create table 表名(
字段名1 字段类型(数字) 约束条件,
字段名2 字段类型(数字) 约束条件,
...
);
说明
- 字段名和字段类型是必须的
- 数字和约束条件是可选的
- 约束条件可以写多个，用空格隔开即可
- 最后一行字段结尾不能加逗号，也就是后括号不能和逗号相接。
字段类型
整型
| 字段类型 | 大小 | 范围（有符号） | 范围（无符号） |
|---|---|---|---|
| tinyint | 1bytes | -27~27 | 0~28 |
| smallint | 2bytes | -215~215 | 0~216 |
| int | 4bytes | -231~231 | 0~232 |
| bigint | 8bytes | -263~263 | 0~264 |
补充
所有的整型都默认带有正负号，如果不想要正负号，则需要添加约束条件：unsigned。
当你在添加记录时，数据大小超出范围：
- 如果是在5.6版本不会报错，会自动处理成最大范围。
- 如果是在5.7及以上版本，则会直接报错。
如果想要让5.6版本在添加超出范围的数据时也报错，需要设置为严格模式，输入以下命令：
set global sql_mode = 'STRICT_TRANS_TABLES';
再重启客户端即可。
但这种方法只是临时修改，如果想要永久的严格模式，在my.ini配置文件中[mysqld]添加下列代码并重启mysql服务即可：
sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY'
浮点型
| 字段类型 | 大小 | 含义 |
|---|---|---|
| float(m,d) | 4bytes | 单精度，总共m位 小数位占d位 |
| double(m,d) | 8bytes | 双精度，总共m位 小数位占d位 |
| decimal(m,d) | m+2bytes | 总共m位 小数位占d位 |
精确度：decimal > double > float
举例
假设一个字段类型为float(6,3)，如果添加数据为123.456789，则会被改成123.456；如果添加数据为123.4，则会被改成123.400；如果添加数据为12.345678，则会被改成12.345；
字符类型
| 字段类型 | 大小 | 含义 |
|---|---|---|
| char(n) | n bytes(n在0~255之间) | 定长字符串 |
| varchar(n) | n bytes(n在0~65535之间) | 变长字符串 |
定长与变长
char(4)：定长，最大只能存储四个字符，超出则报错，不够则空格填充至四个。
varchar(4)：变长，最大只能存储四个字符，超出则报错，不够则有几个存几个。
char与varchar优缺点
char：整存整取，速度快，但会造成一定的存储空间浪费。
varchar：节省存储空间，但存取数据的速度没有char快，因为在存取数据时都需要产生或获取一个1bytes的报头，用于记录数据长度。
拓展
获取字段数据长度方法：char_length()
举例：
select char_length(字段) from 表名;
该方法无法直接获取到定长的真实长度，因为MySQL在存数据的时候会自动填充空格在取数据的时候又会自动移除空格。
让MySQL在取数据的时候不自动移除空命令:
set session sql_mode = 'pad_char_to_full_length'
这个方法只能临时修改，永久修改要添加到my.ini配置文件中。
数字的含义
字段类型括号内的数字大部分情况下是用来限制存储的长度，但是在整型中并不是用来限制长度，而是用来控制展示长度。
如：
/*id字段展示长度为3，不够就用0填充*/
create table t(id int(3) zerofill);
insert into t values(1);
select * from t;
并且整型字段都有一个默认的展示长度。
枚举与集合
| 类型 | 含义 |
|---|---|
| enum(值1,值2,...) | 枚举，添加数据时只能从定义的值中选取，只能选取一个 |
| set(值1,值2,...) | 集合，添加数据时只能从定义的值中选取，可以选取多个或一个 |
举例：
/*枚举*/
create table t1(
gender enum('male','female','others')
);
insert into t1 value ('male');
insert into t1 value ('abc'); /*严格模式下会报错*/
/*集合*/
create table t2(
hobby set('read','run','music')
);
insert into t2 value ('read,run');
insert into t2 value ('read,gg'); /*严格模式下会报错*/
日期类型
| 类型 | 含义 |
|---|---|
| date | 存储年月日 |
| datetime | 存储年月日时分秒 |
| time | 存储时分秒 |
| year | 存储年份 |
约束条件
约束条件是基于字段类型之上的额外限制。
| 约束条件 | 作用 |
|---|---|
| unsigned | 去掉正负号 |
| zerofill | 字段数据长度不够用0填充 |
| not null | 让字段数据不能为空 |
| default | 设置字段默认值 |
| unique | 设置字段数据唯一 |
| primary key | 主键，不能为空且唯一 |
| auto_increment | 自动增长 |
not null
让字段数据不能为空
create table t(
id int,
name varchar(55) not null
);
insert into t values (1,2);
insert into t values (1,''); # 不会报错
insert into t(id) values(1); # 此行会报错
default
设置字段默认值
create table t(
id int,
name varchar(55) default 'tom'
);
insert into t(id) values (1);
insert into t values (1,'mike');
unique
设置字段数据唯一。
单列唯一：某个字段下对应的数据唯一
create table t(
id int unique,
name varchar(55)
);
insert into t values (1,'tom');
insert into t values (1,'mike'); # 报错，id：1已存在
多列唯一：多个字段下对应的数据组合到一起的结果唯一
create table t(
id int,
name varchar(55),
unique(id,name)
);
insert into t values (1,'tom');
insert into t values (1,'mike');
insert into t values (2,'tom');
insert into t values (1,'tom'); # 报错，id=1,name='tom'已经存在
primary key
1.单从约束层面上而言，相当于not null + unique(不能为空且唯一)
create table t(
id int primary key,
name varchar(55)
);
2.InnoDB存储引擎规定的一张表有且必须要有一个主键，用于构建表，如果创建表的时候没有设置主键也没有其他的键，那么InnoDB会采用一个隐藏的字段作为表的主键。
3.如果没有主键但是有不能为空且唯一的字段，那么会自动升级成主键(从上往下的第一个)，如：
create table t(
tid int,
pid int not null unique,
cid int not null unique
);
上述例子会将pid设为主键
auto_increment
自动增长，专门配合主键一起使用。
create table t(
id int primary key auto_increment,
name varchar(55)
);
insert into t(name) value('tom');
insert into t(name) value('mike');
自增特性
1.如果你手动添加自增字段的值，并且你的添加的值比之前的自增字段的值大，那么会从你添加的值开始自增。
create table t(
id int primary key auto_increment,
name varchar(16)
);
# 添加数据
insert into t(name) value('tom'),('mike'),('jake');
# 手动添加自增字段的值
insert into t value(20,'jason');
# 再添加数据
insert into t(name) value('jerry');
# 查询数据
select * from t;
2.自增不会因为删除数据而回退，比如：
create table t(
id int primary key auto_increment,
name varchar(16)
);
# 添加数据
insert into t(name) value('tom'),('mike'),('jake');
# 删除数据
delete from t where id=3;
# 再次添加数据
insert into t(name) value('jason');
# 查询结果
select * from t;
3.删除所有数据并重置
truncate 表名;
