在mysql中的字段约束条件_Mysql 字段类型与约束条件

一、数值类型

1.1 整型

应用场景: id号、年龄...

tinyint:

有符号：默认范围 -128, 127

无符号：默认范围 0，255

#tinyint 默认有符号：

create table t1(id tinyint,

name varchar(16)

);

insert into t1 values(-128,'tank'),(127,'jason');

insert into t1 values(-129,'sean') #报错

select * from t1;

+------+-------+

| id | name |

+------+-------+

| -128 | tank | #有符号，最小值为-128

| 127 | jason | #有符号，最大值127

+------+-------+

#设置无符号tinyint

create table t2(id tinyint unsigned);

insert into t2 values((-1)); #报错

insert into t2 values((0));

insert into t2 values((255));

insert into t2 values((256)); #报错

select * from t2;

+------+

| id |

+------+

| 0 | #无符号，最小值为0

| 255 | #无符号，最大值为255

+------+

int：

有符号：默认范围(-2147483648， 2147483647)

无符号：默认范围 (0，4294967295)

默认宽度为11

#int 默认有符号：

create table t3(id int);

insert into t3 values(-2147483648);

insert into t3 values(-2147483649); #报错

insert into t3 values(2147483647);

insert into t3 values(2147483648); #报错

select * from t3;

+-------------+

| id |

+-------------+

| -2147483648 | #有符号，最小值为-2147483648

| 2147483647 | #有符号，最大值为2147483647

+-------------+

#设置无符号int

create table t4(id int unsigned)

insert into t4 values((-1));

insert into t4 values((0));

insert into t4 values((4294967295));

insert into t4 values((4294967296));

select * from t4;

------------+

| id |

+------------+

| 0 | #无符号，最小值为0

| 4294967295 | #无符号，最大值为4294967295

+------------+

1.2 浮点型

应用场景：存储薪资、身高、体重、体质参数等

float ：FLOAT[(M,D)] ，单精度浮点数(非准确小数值)

double：DOUBLE[(M,D)]，双精度浮点数(非准确小数值)

以上两个的m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

decimal：DECIMAL[(M,D)]，准确的小数值

m最大值为65，d最大值为30。

# 范围255是最大长度(包括.小数), 30代表是小数的位数

create table t5(x float(255, 30));

create table t6(x double(255, 30));

create table t7(x decimal(65, 30));

# 三种浮点型: 区别在于精确度不一样

insert into t5 values(1.111111111111111111111111111111);

insert into t6 values(1.1111111111111111111111111111);

insert into t7 values(1.1111111111111111111111111111);

#随着小数的增多，精度开始不准确

mysql> select * from t5;

+----------------------------------+

| x |

+----------------------------------+

| 1.111111164093017600000000000000 |

#精度比float要准确点，但随着小数的增多，同样变得不准确

mysql> select * from t6;

+----------------------------------+

| x |

+----------------------------------+

| 1.111111111111111200000000000000 |

#精度始终准确,d为30，于是只留了30位小数

mysql> select * from t7;

+----------------------------------+

| x |

+----------------------------------+

| 1.111111111111111111111111111100 |

+----------------------------------+

1.4 字符串类型

char： 定长，简单粗暴，浪费空间，存取速度快。

字符长度范围：0-255(一个中文是一个字符，是utf8编码的3个字节)。

存储char类型的值时，会往右填充空格来满足长度

create table t8(id int,name char(4));

insert into t8 values(1, 'tank');

insert into t8 values(1, 't');

insert into t8 values(1, '你个大傻子'); #字符长度超过4报错

mysql> select * from t8;

+------+--------------+

| id | name |

+------+--------------+

| 1 | tank |

| 1 | t | # t+3个空格

+------+--------------+

varchar： 不定长字符，精准，节省空间，存取速度慢

存几个字符，就是几个字符的大小，每个字符前都要+1bytes

create table t9(id int, name varchar(4));

insert into t9 values(1, 'bob'); # 1bytes + bob

insert into t9 values(2, 'tank'); # 1bytes + tank

insert into t9 values(3, 'sean'); # 1bytes + sean

insert into t9 values(4, 'jason'); #报错

mysql> select * from t9;

+------+------+

| id | name |

+------+------+

| 1 | bob |

| 2 | tank |

| 3 | sean |

+------+------+

二、日期类型

例如：

date: 2019-12-11

datetime: 2019-12-11 11:11:11

time: 11:11:11

year: 2019

timestamp: 时间戳

注意：TIMESTAMP的字段默认不为空(not null),默认值为当前时间(CURRENT_TIMESTAMP)，如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。

create table student(

id int,

name varchar(10),

birth date,

register datetime,

born_year year,

t_time time,

update_time timestamp

);

insert into student values(1, 'baohan', '1996-06-23', '2019-12-12 11:11:11', '1996', '11:11:11', null);

insert into student values(2, 'HCY', '1000-11-11', '1980-11-11 11:11:11','2019', '11:11:11', null);

mysql> select * from student;

+------+--------+------------+---------------------+-----------+----------+---------------------+

| id | name | birth | register | born_year | t_time | update_time |

+------+--------+------------+---------------------+-----------+----------+---------------------+

| 1 | baohan | 1996-06-23 | 2019-12-12 11:11:11 | 1996 | 11:11:11 | 2019-12-11 16:02:45 |

| 2 | HCY | 1000-11-11 | 1980-11-11 11:11:11 | 2019 | 11:11:11 | 2019-12-11 16:03:22 |

+------+--------+------------+---------------------+-----------+----------+---------------------+

三、枚举与集合

字段的值只能在给定范围中选择，如单选框，多选框

enum： 单选 只能在给定的范围内选一个值(多选一)，如性别 sex 男male/女female

set ： 多选 在给定的范围内可以选择一个或一个以上的值 ，如(爱好1,爱好2,爱好3...)

#enum:多选一

create table t10(id int, name varchar(4), gender enum('male', 'female', 'others'));

# insert into 表名(字段名) values(字段名对应的值);

insert into t13(id, name, gender) values(1, 'tank', 'male');

insert into t13(id, name, gender) values(1, 'tank', '人妖'); #报错，‘人妖’不在范围内

mysql> select * from t10;

+------+------+--------+

| id | name | gender |

+------+------+--------+

| 1 | tank | male |

+------+------+--------+

#set 可 多选一 或 多选多

create table t11(

id int,

name varchar(4),

hobbies set('read', 'sing', 'run', 'think')

);

insert into t11 values(1, 'bob', 'read,sing');

mysql> select * from t11;

+------+------+-----------+

| id | name | hobbies |

+------+------+-----------+

| 1 | bob | read,sing |

+------+------+-----------+

四、约束条件

初始约束条件：not null

#约束插入记录时id不能为空

create table t1(id int not null,name varchar(6));

insert into t1 values(1,'tank');

insert into t1 values(null,'tank'); #报错

mysql> select * from t1;

+----+------+

| id | name |

+----+------+

| 1 | tank |

+----+------+

unique： ：唯一，将某个字段设置为唯一的值

create table t2(id int not null unique,name varchar(4));

insert into t2(id, name) values(1, 'tank'), (2, 'sean');

insert into t2(id, name) values(1, 'bob'); #报错，1已用过

+----+------+

| id | name |

+----+------+

| 1 | tank |

| 2 | sean |

+----+------+

primary key(主键)----> not null unique： pk就是表中的索引: 可以通过索引快速查找某些数据，提高查询效率。

# 将id设置为主键，非空且唯一

create table t3(id int primary key,name varchar(4));

insert into t3(id, name) values(1, 'tank');

insert into t3(id, name) values(2, 'sean');

insert into t3(id, name) values(1, 'bob'); #报错

mysql> desc t3;

+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| name | varchar(4) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

auto_increment：自增

# 将id设置为自增

create table t4(id int primary key auto_increment,name varchar(4));

# 自增默认从0开始

insert into t4(name) values('tank');

insert into t4(name) values('sean');

insert into t4(name) values('bob');

insert into t4(name) values('哈哈哥');

mysql> select * from t4;

+----+-----------+

| id | name |

+----+-----------+

| 1 | tank |

| 2 | sean |

| 3 | bob |

| 4 | 哈哈哥 |

+----+-----------+

## 若想自增从指定值开始，可插入第一条数据时先指定id的值;

insert into t4(id, name) values(10, 'tank');

insert into t4(name) values('sean'); # 11

insert into t4(name) values('bob'); # 12

insert into t4(name) values('哈哈哥'); # 13

mysql> select * from t4;

+----+-----------+

| id | name |

+----+-----------+

| 10 | tank |

| 11 | sean |

| 12 | bob |

| 13 | 哈哈哥 |

+----+-----------+

zerofill：使用0填充空格

create table t5(id int zerofill);

insert into t5 values(100);

mysql> select * from t5;

+------------+

| id |

+------------+

| 0000000100 |

+------------+

default：约束插入数据时的默认值

create table t7(id int,

name varchar(32) unique,

password varchar(255) default '000000');

insert into t7(id,name) values(1,'tank');

mysql> select * from t7;

+------+------+----------+

| id | name | password |

+------+------+----------+

| 1 | tank | 000000 |

+------+------+----------+

delete：清空表中所有记录：

truncate：清空表中的所有记录，并且id重置为0

#delete

create table t6(id int primary key auto_increment,name varchar(4));

insert into t6(name) values('tank');

insert into t6(name) values('大大大'), ('sean');

delete from t6;

insert into t6(name) values('tank');

insert into t6(name) values('大大大'), ('sean');

mysql> select * from t6;

+----+-----------+

| id | name |

+----+-----------+

| 4 | tank |

| 5 | 大大大 |

| 6 | sean |

+----+-----------+

#truncate

create table t6(id int primary key auto_increment,name varchar(4));

insert into t6(name) values('tank');

insert into t6(name) values('sean');

truncate table t6;

insert into t6(name) values('tank');

insert into t6(name) values('sean');

mysql> select * from t6;

+----+-----------+

| id | name |

+----+-----------+

| 1 | tank |

| 2 | sean |

+----+-----------+

五、设置严格模式

# 查看数据库配置中变量名包含mode的配置参数:

show variables like "%mode%";

# 修改安全模式:

set session; # 局部有效，只在你当前操作的窗口有效

set global session; # 全局有效，永久有效

# 修改完之后退出当前客户端重新登录即可，不用关闭服务端

set global sql_mode = 'STRICT_TRANS_TABLES';