1-MySQL - 数据类型和约束

目录

数据类型

在MySQL中，我们需要了解的数据类型共有以下几种：

  • 数值类型。
  • 日期类型。
  • 字符串类型。
  • ENUM和SET类型。

我们一一来看看吧。

数值类型

MySQL支持所有标准SQL数值类型。包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

MySQL支持的整数类型有TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

对于小数的表示，MYSQL分为两种方式：浮点数和定点数。浮点数包括float(单精度)和double(双精度),而定点数只有decimal一种，在mysql中以字符串的形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1字节 (-128, 127) (0, 255) 小整数值
SMALLINT 2字节 (-32768, 327667) (0, 65535) 大整数值
MEDIUMINT 3字节 (-8388608, 8388697) (0, 16777215) 大整数值
INT or INTEGER 4字节 (-2147483648, 2147483647) (0, 4294967295) 大整数值
BIGINT 8字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 float(255,30) (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值
DOUBLE 8 字节double(255,30) (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

int类型

这里我们先以int为例展开讨论。

create table t1(n1 int(4));
desc t1;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| n1    | int(4) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
insert into t1 values(11);
insert into t1 values(111111);
select n1 from t1;
+--------+
| n1     |
+--------+
|     11 |
| 111111 |
+--------+

由最后的查看结果，我们为int类型设定的宽度为4，结果插入一个6位的也行。这是怎么回事？

create table t2(n1 int(4) zerofill);
desc t2;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| n1    | int(4) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
insert into t2 values(11);
insert into t2 values(111111);
select n1 from t2;
+--------+
| n1     |
+--------+
|   0011 |
| 111111 |
+--------+

可以看到，我们在创建表的时候，为n1字段加上zerofill，表示不够4位就填充0。而最后的查询结果告诉我们，如果为int类型指定宽度，则是显示字符的宽度（字符数量），超过这个限制也会显示。

而查询表结构的时候，有个unsigned，这是无符号的类型。那这是什么意思呢？

create table t3(n1 int);
desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| n1    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
insert into t3 values(11111111111111111111111111);
insert into t3 values(-11111111111111111111111111);
select n1 from t3;
+-------------+
| n1          |
+-------------+
|  2147483647 |
| -2147483648 |
+-------------+

首先，desc告诉我们int类型的默认显示宽度是11位，而最大表示数值范围是2147483647，如果你插入的数据是超过这个范围的话。而2147483647的显示宽度是10位，为什么不是默认的11位呢？这是因为int类型默认类型是有符号的，而有符号的就要考虑正号和负号，而符号仅用1位就能表示。

原因如下：

int的存储宽度是4个Bytes，即32个bit，即2^32

无符号最大值为：4294967296-1

有符号最大值：2147483648-1

有符号和无符号的最大数字需要的显示宽度均为10，而针对有符号的最小值则需要11位才能显示完全，所以int类型默认的显示宽度为11是非常合理的。

那么如何设置一个无符号的呢？

create table t4(n1 int unsigned);
desc t4;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| n1    | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
insert into t4 values(11111111111111111111111111);
select n1 from t4;
+------------+
| n1         |
+------------+
| 4294967295 |
+------------+

无符号的需要在int类型指定unsigned。结果也是没错的。都最开始列举的表中数据一致。

最后：int类型，其实没有必要指定显示宽度，使用默认的就行；如果你的整数范围超过int类型范围，请选用别的数据类型；并且默认的，我们创建的int类型是有符号类型。

float类型

先来看定义：

FLOAT[M, D] [UNSIGNED] [ZEROFILL]
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
DECIMAL[(m[,d])] [unsigned] [zerofill]

float表示单精度浮点数（非准确小数值），M表示数字总个数，最大值是255；D是小数点后的数字个数，最大值30。也就是说，如果float(255,30)意思是，小数位是30位，而整数位就不是255了，而是255-30=225位。它的精准度：随着小数的增多，精度变得不准确。

双精度（double）浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30。它的精准度：随着小数的增多，精度比float要高，但也会变得不准确。

而decimal的准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。它的精准度：随着小数的增多，精度始终准确；对于精确数值计算时需要用此类型。decaimal能够存储精确值的原因在于其内部按照字符串存储。

create table f1(weight float(256,30));
ERROR 1439 (42000): Display width out of range for column 'weight' (max = 255)  # 说显示宽度超过了255
create table f2(weight float(255,31));
ERROR 1425 (42000): Too big scale 31 specified for column 'weight'. Maximum is 30.  # 告诉我们小数点后的位数最多30位
create table f3(weight float(255,30));   # 这样就没问题了
desc f3;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| weight | float(255,30) | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

首先，我们创建的float类型是有符号类型。

同样的，你想创建一个无符号的，也要指定unsigned。

create table f4(weight float(255,30) unsigned);
desc f4;
+--------+------------------------+------+-----+---------+-------+
| Field  | Type                   | Null | Key | Default | Extra |
+--------+------------------------+------+-----+---------+-------+
| weight | float(255,30) unsigned | YES  |     | NULL    |       |
+--------+------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

在使用浮点型的数据时，我们考虑的核心是关注它们的精度。来看对比。

create table f5(weight float(255,30) unsigned);
create table f6(weight double(255, 30) unsigned);
create table f7(weight decimal(65, 30) unsigned);
insert into f5 values(1.111111111111111111111111111111111111111111111111111);
insert into f6 values(1.111111111111111111111111111111111111111111111111111);
insert into f7 values(1.111111111111111111111111111111111111111111111111111);

我们创建三张不同类型的表，并插入一些数据，并且这些小数位都超过30位。来观察他们的精度：

select weight from f5;
+----------------------------------+
| weight                           |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
select weight from f6;
+----------------------------------+
| weight                           |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
select weight from f7;
+----------------------------------+
| weight                           |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+

由各自的查询结果可以看到，float类型的精度只有前7位是精确的；double类型的精度是15位；而decimal则保留完整的精度，毕竟是字符串形式的存储么。
但是decimal虽然精度较高，但是它也是有限制的，因为它的数字总大小为65位，所以抛出小数位的30位，还剩30位整数位。

最后，最后，这里只是说的显示宽度仅是在int中使用，其他数据类型宽度是存储限制。比如BIT类型。

BIT

create table b1(b bit(1));
desc b1;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| b     | bit(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
insert into b1 values(0);
insert into b1 values(1);
insert into b1 values(2);
select b from b1;
+------+
| b    |
+------+
|      |
|     |
|     |
+------+

首先了解，字段b的类型是bit，宽度是1，那能表示多少数值呢，一个bit，只能表示0和1两个。但是通过查询发现，跟我们想要的结果不一样。

这是为什么，bit类型存储是以二进制存储到硬盘上的。所以，我们想要查询到我们想要的值，还要借助MySQL提供给我们的函数bin()和hex()，意思是返回二进制值的字符串形式表示和十六进制的表示形式。

select bin(b) from b1;
+--------+
| bin(b) |
+--------+
| 0      |
| 1      |
| 1      |
select hex(b) from b1;
+--------+
| hex(b) |
+--------+
| 0      |
| 1      |
| 1      |
+--------+

可以看到，字段b的bit(1)类型只能表示0和1，而插入的2超出了范围。所以，你在用的时候，需要注意：

create table b2(b bit(2));
insert into b2 values(2);
select bin(b) from b2;
+--------+
| bin(b) |
+--------+
| 10     |
+--------+

可以看到，2的二进制形式是10。

时间类型

日期类型有：

  • DATE（YYYY-MM-DD（1000-01-01/9999-12-31）），2019-07-31，出生年月日
  • TIME（HH:MM:SS（'-838:59:59'/'838:59:59'）），16:40:40，下班时间
  • DATETIME（YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y））、2019-07-31 16:40:40，注册时间、文章发布时间、员工入职时间
  • TIMESTAMP（YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时））、2019-07-31 16:40:40
  • YEAR（YYYY（1901/2155））、2019，历史大事件，出生年

来个示例：

create tabled1(
    born_date date, 
    get_time time, 
    reg_time datetime, 
    born_year year    # 最后一个字段后面不要有逗号
);
insert into d1 values(
	'1999-11-11',
	'18:30:00',   
	'2018-11-11 11:11:11',
	'1999'  # 不要写成18/30/30
);

来查询：

desc d1;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| born_date | date     | YES  |     | NULL    |       |
| get_time  | time     | YES  |     | NULL    |       |
| reg_time  | datetime | YES  |     | NULL    |       |
| born_year | year(4)  | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
select * from d1;
+------------+----------+---------------------+-----------+
| born_date  | get_time | reg_time            | born_year |
+------------+----------+---------------------+-----------+
| 1999-11-11 | 18:30:00 | 2018-11-11 11:11:11 |      1999 |
+------------+----------+---------------------+-----------+

再来掌握一个now()函数：

insert into d1 values(now(), now(), now(), now());
select * from d1;
+------------+----------+---------------------+-----------+
| born_date  | get_time | reg_time            | born_year |
+------------+----------+---------------------+-----------+
| 1999-11-11 | 18:30:00 | 2018-11-11 11:11:11 |      1999 |
| 2019-07-31 | 16:57:51 | 2019-07-31 16:57:51 |      2019 |

由第二行记录可以发现，各类型都按照自己的规则截取所需的日期数据。

了解：datetime与timestamp的区别

create table d2(x datetime, y timestamp);
desc d2;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| x     | datetime  | YES  |     | NULL              |                             |
| y     | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
insert into d2 values(Null, Null);
insert into d2 values('1111-11-11','1111-11-11');
 select * from d2;
+---------------------+---------------------+
| x                   | y                   |
+---------------------+---------------------+
| NULL                | 2019-07-31 17:05:43 |
| 1111-11-11 00:00:00 | 0000-00-00 00:00:00 |
+---------------------+---------------------+

通过上述验证分析，虽然这两种日期格式，都能满足我们大多数使用场景，但是在某些情况下，它们也有自己的优劣之分，来看看它们的区别：

  • DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
  • DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置。
  • DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
  • DATETIME的默认值为null，TIMESTAMP的字段默认不为空（not null），默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。

字符串类型

字符串类型这里需要重点掌握的就是char和varchar两个，存放名字、性别、密码、文本内容等等。

先看它们的区别，注意，长度指的是字符的长度：

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

    • 字符长度范围：0~255，一个汉字是一个字符，utf8编码一个普通汉字占用3个字节。
    • 存储：如果存储的值，不满足指定的长度时，会往右填充空格来满足长度，例如指定长度为10，存储大于10个字符报错，小于10个字符会用空格填充，凑够十个字符。
    • 查询（或称检索）：查询出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式
  set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'

  • varchar，可变长度，存储更精确，但存取速度慢。

    • 字符长度范围：0-65535，在utf8编码中，如果大于21844会提示用其他类型 。mysql行最大限制为65535字节。
    • 存储：varchar类型存储的是真实内容，而不是用空格填充，如存储abc ，尾部的空格也会被存储起来。注意，varchar类型会在真实数据前面加1~2个Bytes前缀，用来存储真实数据的bytes字节数，1~2Bytes最大表示65535个数字，正好符合MySQL对row的最大字节限制。如果真实的数据小于255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）；如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）。
    • 查询：尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容。

char VS varchar：存储范围验证

create table c1(s char(256));
ERROR 1074 (42000): Column length too big for column 's' (max = 255); use BLOB or TEXT instead
create table c2(s char(255));
create table c3(s varchar(21845));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
create table c4(s varchar(21844));
create table c5(s varchar(65534));

通过打印结果，可以看到，char类型，如果长度超过255，就提示我们字段长度最大是255；varchar的列长度如果超过21844，提示我们varchar类型的最大行大小为65535。

但是最后的c5却成功创建，这是为什么呢？我们来看它的表结构：

desc c4;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| s     | varchar(21844) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
desc c5;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| s     | mediumtext | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

可以看到，c5表的字段类型已经变成了mediumtext，而不是varchar类型。

char VS varchar：存储长度验证

create table c6(s char(3));
create table c7(s varchar(3));
insert into c6 values('abcd');
insert into c6 values('生存还是毁灭');
insert into c7 values('abcd');
insert into c7 values('生存还是毁灭');

再来看查询结果：

select s from c6;
+-----------+
| s         |
+-----------+
| abc       |
| 生存还    |
+-----------+
select s from c7;
+-----------+
| s         |
+-----------+
| abc       |
| 生存还    |
+-----------+

可以看到，无论是char还是varchar；无论是中文还是其他，它们限制的是字符个数。

char VS varchar：定长与可变长度

再来研究它们之间的特点的区别，也就是定长和可变长度的区别。

我们通过表格来看看他们的存储关系：

Value CHAR(4) 存储需求 VARCHAR(4) 存储需求
'' ' ' 4 bytes '' 1 bytes
'ab' 'ab ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefg' 'abcd' 4 bytes 'abcd' 5 bytes

不要被5bytes所迷惑，abcd占4个字节，还有一个字节存储该字符串的长度。

先了解两个函数：

  • length：查看字节数。
  • char_length：查看字符数。
create table c8(s1 char(3), s2 varchar(3));
desc c8;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| s1    | char(3)    | YES  |     | NULL    |       |
| s2    | varchar(3) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
insert into c8 values('a', 'b');
select s1, s2 from c8;
+------+------+
| s1   | s2   |
+------+------+
| a    | b    |
+------+------+

现在看是啥也看不出来，所以，我们用上char_length函数：

select char_length(s1), char_length(s2) from c8;
+-----------------+-----------------+
| char_length(s1) | char_length(s2) |
+-----------------+-----------------+
|               1 |               1 |
+-----------------+-----------------+

这也看不出来啥呀，a和b不就是各占用一个字符长度么。

这是因为啊，我们在查询char类型数据的时候，MySQL会默默的删除尾部的空格（装作我并没有浪费空间！），我们来让它现原形：

SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
select char_length(s1), char_length(s2) from c8;
+-----------------+-----------------+
| char_length(s1) | char_length(s2) |
+-----------------+-----------------+
|               3 |               1 |
+-----------------+-----------------+

这个时候再看，是不是现原形了，char类型占用指定的3个字符宽度，当然，一个英文字符也占用一个字节。而varchar就占用一个字符。

中文也一样：

insert into c8 values('你', '好');
select char_length(s1), char_length(s2) from c8;
+-----------------+-----------------+
| char_length(s1) | char_length(s2) |
+-----------------+-----------------+
|               3 |               1 |
|               3 |               1 |
+-----------------+-----------------+

这就是我们使用char和varchar时需要注意的点。

小结：

InnoDB存储引擎：建议使用VARCHAR类型
单从数据类型的实现机制去考虑，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。

但对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。
其他字符串系列（效率：char>varchar>text）

  • TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
  • BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB
  • BINARY系列 BINARY VARBINARY

text：text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

枚举与集合

有些情况，我们需要在一堆选项中选择一个，或者选择多个，如单选框和复选框。
那，在MySQL的字段中，字段的类型也可以有单选和多选。

  • enum单选，只能在给定范围内选一个值，如性别；适用于给定范围后续不会发生变化的场景；另外数字类型不适用枚举。
  • set多选，在给定的范围选择多个值，如爱好。
create table user1(
	id int,
	name char(5),
	sex enum('male', 'female', 'unknow'),
	hobby set('eat', 'sleep', 'play mobile phone')
);
desc user1;
+-------+----------------------------------------+------+-----+---------+-------+
| Field | Type                                   | Null | Key | Default | Extra |
+-------+----------------------------------------+------+-----+---------+-------+
| id    | int(11)                                | YES  |     | NULL    |       |
| name  | char(5)                                | YES  |     | NULL    |       |
| sex   | enum('male','female','unknow')         | YES  |     | NULL    |       |
| hobby | set('eat','sleep','play mobile phone') | YES  |     | NULL    |       |
+-------+----------------------------------------+------+-----+---------+-------+
insert into user1 values(1, '张三', 'male', 'eat,sleep');
select * from user1;
+------+-----------+------+-----------+
| id   | name      | sex  | hobby     |
+------+-----------+------+-----------+
|    1 | 张三      | male | eat,sleep |
+------+-----------+------+-----------+

如果是set类型，多个参数以逗号隔开。
这里，我们也可以设置默认值，如果用户不填写的话：

create table user2 (id int, sex enum('male', 'female', 'unknow') default 'male');
insert into user2(id) values(1);
insert into user2 values(2, 'female');
select * from user2;
+------+--------+
| id   | sex    |
+------+--------+
|    1 | male   |
|    2 | female |
+------+--------+

根据查询结果可以看到，如果性别字段传值就用我们传的值，不传就用默认的。

约束

约束条件与数据类型一样的宽度一样，都是可选参数。

作用是用于保证数据的完整性和一致性，以免不符合规范的数据写入数据库。

在MySQL中，主要约束有：

  • PRIMARY KEY (PK)，标识字段为该表的主键，可以唯一标识某一条记录，及设置字段非空且唯一。
  • FOREIGN KEY (PK)，标识该字段为该表的外键。
  • NOT NULL：约束字段不能为空。
  • UNIQUE，标识该字段的值是唯一的。
  • AUTO_INCREMENT，标识该字段的值自动增长（整数类型，而且为主键）。
  • DEFAULT，为字段设置默认值，比如设置性别默认为男。
  • UNSIGNED，设置整形类型为无符号类型。
  • ZEROFILL，使用0填充。

not null

是否可空，null表示空，非字符串。

not null 不可空，null可空。

create table n1(
	id int not null,
    name char(5) not null,
    age int 
);
desc n1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
| name  | char(5) | NO   |     | NULL    |       |
| age   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
insert into n1 values(1, '张开1', 18);
insert into n1 values(2, null, 20);
ERROR 1048 (23000): Column 'name' cannot be null
insert into n1 values(null, '张开2', 30);
ERROR 1048 (23000): Column 'id' cannot be null
select * from n1;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | 张开1   |   18 |
+----+---------+------+

可以看到，当字段设置了not null的后，我们就必须为该字段传值。

not null的坑

来，做个试验，刚才不是说not null的作用是约束字段不能为空么？我们再来创建个表：

create table n2(
	id int not null,
    name char(5) not null,
    age int
);
insert into n2 values(1, '张开1', 20);
insert into n2 values(2, null, 21);
insert into n2 values(null, '张开2', 22);
select * from n2;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | 张开1   |   20 |
+----+---------+------+

如果你查询的话，会发现跟之前的n1表一模一样，哪有问题呀？那好，我们再来插入数据：

insert into n2 values(null, null, null), (2, null, 23), (null, '张开腿', 24);
Query OK, 3 rows affected, 4 warnings (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 4
select * from n2;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | 张开1     |   20 |
|  0 |           | NULL |
|  2 |           |   23 |
|  0 | 张开腿    |   24 |
+----+-----------+------+

这是什么情况？not null也不管用了啊！参照官网的解释说，MySQL服务器可以在不同的SQL模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于sql_mode系统变量的值。所以，机缘巧合下，发生了这种事情。那么就从sql_mode下手，先来看当前的sql_mode：

show variables like "sql_mode";
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+

可以看到，sql_mode只有一个关于NO_ENGINE_SUBSTITUTION设置，意思是MySQL在创建表的时候，可以指定engine子句，也就是指定表的存储引擎。

ps：如果你的结果不是这样的， 你可手动将sql_mode设置为空，来演示这个效果：

set sql_mode="";

抛开这个不管，我们发现并没有什么关于约束之类的设置（我们称为设置严格模式）。那我们就给它设置上，设置有两种方式。第一是在当前客户端设置：

set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

这么一设置，立马见效：

create table n3(
	id int not null,
    name char(5) not null,
    age int
);
insert into n3 values(1, '张开1', 20);
insert into n3 values(null, null, null), (2, null, 23), (null, '张开腿', 24);
ERROR 1048 (23000): Column 'id' cannot be null
select * from n3;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | 张开1   |   20 |
+----+---------+------+

可以看到，只有第一次的插入生效了。完美！但是，这么着，如果服务器重启就生效了，想要一劳永逸的话，还是要从配置文件下手，我们可以修改MySQL的配置文件my.ini：

[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后重启服务。

default

如果某一列字段经常用重复的内容，在我们频发插入的时候，就比较麻烦。所以，我们要是使用default默认值来减少麻烦。比如一个班级内有95%的都是男生，只有5%的女生，那么在插入学生数据的时候，我们就可以为性别这一列给一个默认值是男生：

create table d1(
	id int not null,
    name char(5) not null,
    gender enum('male', 'female', 'unknow') not null default 'male'
);
insert into d1 values(1, '小明', 'male');
insert into d1 values(2, '小红', 'female');
insert into d1 values(1, '小李');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
select * from d1;
+----+--------+--------+
| id | name   | gender |
+----+--------+--------+
|  1 | 小明   | male   |
|  2 | 小红   | female |
+----+--------+--------+

我们原本以为gender字段是有默认值，就可以不写，但是，如上那么写，出了错。这是因为MySQL并不能理解我们是想要使用gender的默认值。而是认为你少了一个字段没有填写。怎么办呢，怎么应用上默认值呢：

insert into d1(id, name) values(3, '小李');
select * from d1;
+----+--------+--------+
| id | name   | gender |
+----+--------+--------+
|  1 | 小明   | male   |
|  2 | 小红   | female |
|  3 | 小李   | male   |
+----+--------+--------+

比如按照上面的方式，告诉MySQL指定字段传值，gender字段不传值，就使用默认值吧。

default可以这么用：

create table d1(
	id int not null,
    name char(5) not null,
    age int default 18,
    gender enum('male', 'female', 'unknow') not null default 'male'
);

比如直接为age字段设置默认值，而无需指定not null。但是我们一般是default和not null连用。

auto_increment

针对数字列，顺序的自动填充数据，默认从1开始，也可以设置步长和偏移量；常用来设置自增id。

create table a0(
    id int primary key auto_increment,
    name char(5)
);
desc a0;

insert into a0(name) values ("张开"), ("李开"), ("张不开");

select * from a0;

上面是默认的自增长配置，现在来一个修改初始自增id初始值的配置：

-- 创建表时指定
CREATE TABLE a1(id INT PRIMARY KEY AUTO_INCREMENT, NAME CHAR(3)) ENGINE='innodb' AUTO_INCREMENT=5;
-- 或者使用alter语句修改
ALTER TABLE tbl AUTO_INCREMENT = 100;
	
SHOW CREATE TABLE a1;

INSERT INTO a1(name) VALUES('11'),('22');
-- id值将会从5开始
SELECT * FROM a1;

再来了解一个不常用的配置，就是修改自增id的初始值和步长：

  • 设置自增初始值：auto_increment_offset。
  • 设置自增步长：auto_increment_increment。

比如自增id从2开始，然后步长是2，怎么设置呢？有两种方式可以设置。

基于session级别的配置

也就是针对本次会话有效，来看示例：

set session auto_increment_increment=2;
set session auto_increment_offset=4;

create table a2(
    id int primary key auto_increment,
    name char(5)
);

insert into a2(name) values ('张开'),('李开');
select * from a2;

但是，这仅仅是针对本次会话，当前会话关闭后，就失效了。

基于全局级别的配置

-- 也可以在配置文件中配置
set global auto_increment_increment=2;
set global auto_increment_offset=2;

create table a3(
    id int primary key auto_increment,
    name char(5)
);

insert into a3(name) values ('张开'),('李开');
select * from a3;

如果你是终端执行的，请quit退出重新登录才能生效。

另外，auto_increment_increment和auto_increment_offset这两个变量会相互影响，来自官网：

When the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.

当auto_increment_offset的值大于auto_increment_increment时，auto_increment_offset的值将被忽略，也就是说只有步长生效，初始值还是从1开始。

来个不生效的例子：

mysql> set session auto_increment_increment=2;
Query OK, 0 rows affected (0.00 sec)

mysql> set session auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%incre%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| auto_increment_increment    | 2     |
| auto_increment_offset       | 3     |
| div_precision_increment     | 4     |
| innodb_autoextend_increment | 64    |
+-----------------------------+-------+
4 rows in set (0.01 sec)

mysql> create table a4(id int primary key auto_increment,name char(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a4(name) values('aa'),('bb');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from a4;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  3 | bb   |
+----+------+
2 rows in set (0.00 sec)

注意，这两个值不常用，记得改回来。

通常，一个表中只能有一个自增长字段，并且该字段必须被约束为key，这个key可以是unique或者primary key。

key

约束中的key有三种：

  • unique
  • primary key
  • foreign key

其中unique和primary key可以提高查询速度，而foreign key用来表与表之间的关系的。

unique

create table u1(
    id int unique,
    name char(5) unique
);
insert into u1(name) values ('张开'),('李开');
select * from u1;

如果id或name字段有重复则会报错。

primary key

primary key指主键，主键的效果非空且唯一，可以加速查询。

create table p1(
    id int primary key, 
    name char(3)
);
desc p1;

primary key等价于 not null unique：

create table p2(
    id int not null unique primary key,
    name char(3)
);
desc p2;

也可以使用constraint定义约束：

constraint 约束名称 约束类型 (约束字段)

示例：

create table p3(
    id int,
    name char(3),
    constraint primary_id primary key (id)
);
desc p3;

当然，也可以省略constraint关键字：

create table p4(
    id int,
    name char(3),
    primary key (id)
);
show create table p4;

注意，一张表中，只能有一个主键，但是可以有多个not null unique。

create table p5(
    id int not null unique ,
    name char(3) not null unique
);
show create table p5;

联合唯一

create table p6(
    id int primary key auto_increment,
    ip char(15) not null ,
    port int not null ,
    unique (ip, port)
);
show create table p6;

insert into p6(ip, port) values
('1.1.1.1', 3306),
('1.1.1.0', 3306),
('1.1.1.1', 8080);

select * from p6;

将ip和端口设置为联合唯一，保证数据唯一性。

foreign key

为了快速理解foreign key，我们这准备两张表来进行关联：

  • 部门表，也称被关联表，父表，一个部门可以关联多个员工。
  • 员工表(工号，姓名，所属部门)，子表，一个员工只属于一个部门。

分析：表与表之间的关系

分析步骤：

  1. 先站在左表的角度去找

    • 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）

  2. 再站在右表的角度去找

    • 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

  3. 总结：

    • 多对一：

      • 如果只有步骤1成立，则是左表多对一右表
      • 如果只有步骤2成立，则是右表多对一左表

    • 多对多：

      • 如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

    • 一对一:

      • 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

来看如何使用foreign key：

-- 表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME CHAR(32) NOT NULL
)ENGINE=INNODB CHARSET=utf8;

-- 父表先插入数据，便于子表关联
INSERT INTO department(NAME) VALUES
('人事部'),
('财务部'),
('行政部');

-- department_id外键关联到父表的id字段，并且指定同步更新和删除
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL,
department_id INT,
CONSTRAINT fk_name FOREIGN KEY(department_id) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB CHARSET=utf8;

INSERT INTO employee(NAME, department_id) VALUES
('zhang3',1),
('li4',2),
('wang5',3),
('zhao6',2),
('wang7',1),
('qian9',3),
('sun10',2),
('zhou11',1);

来看示例：

-- 当父表的数据被修改，同时子表的相关数据也同步修改
UPDATE department SET id=4 WHERE id=1;  -- 将id为1的修改为4，
SELECT * FROM employee;   -- 子表的关联id也随之改变

-- 当父表的数据被删除，子表的相关记录也被同步删除
DELETE FROM department WHERE id=4;
SELECT * FROM employee;   -- 子表的关联字段也被随之删除

一对多/多对一/多对多之间建立关系示例

三张表：

  • 出版社表
  • 作者信息
  • 书籍表
-- 出版社表
CREATE TABLE publisher(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32)
)ENGINE=INNODB CHARSET=utf8;

INSERT INTO publisher(id,NAME) VALUES
(1,'清华出版社'),
(2,'北大出版社'),
(3,'机械工业出版社'),
(4,'邮电出版社'),
(5,'电子工业出版社'),
(6,'人民大学出版社');


-- 书籍表,该表和出版社的关系是多对一，一家出版社可以出版多本书，所以，foreign key要在书籍表中建立，绑定到出版社表的id字段
CREATE TABLE book(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32),
pub_id INT NOT NULL,
FOREIGN KEY(pub_id) REFERENCES publisher(id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB CHARSET=utf8;


INSERT INTO book(NAME,pub_id) VALUES
('book1',1),
('book2',2),
('book3',3),
('book4',2),
('book5',3),
('book6',6),
('book7',1),
('book8',3);


-- 作者表，由于一本书可以有多个作者，一个作者也可以写多本书，所以，作者表和书籍表的关系是多对多，需要使用第三张表来建立多对多关系
CREATE TABLE author(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL
)ENGINE=INNODB CHARSET=utf8;


INSERT INTO author(NAME) VALUES
('张开1'),
('张开2'),
('张开3'),
('张开4'),
('张开5'),
('张开6');


-- 作者表和书籍表的多对多关系，建立的第三张表
CREATE TABLE author2book(
id INT NOT NULL UNIQUE AUTO_INCREMENT,
author_id INT NOT NULL,
book_id INT NOT NULL,
FOREIGN KEY(author_id) REFERENCES author(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY(book_id) REFERENCES book(id) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=INNODB CHARSET=utf8;

INSERT INTO author2book(author_id,book_id) VALUES
(1,1),
(1,2),
(2,1),
(3,3),
(3,4),
(1,3);

一对一建立关系

两张表：

  • 学生表，学生一定是客户。
  • 客户表，客户不一定是学生，但学生一定是客户。
-- 客户表
CREATE TABLE customer(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL COMMENT '客户姓名'
)ENGINE=INNODB CHARSET=utf8;

INSERT INTO customer(NAME) VALUES
('张开1'),
('张开2'),
('张开3'),
('张开4'),
('张开5'),
('张开6');

-- 学生表,学生表的姓名字段从关联表中获取
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
class_name VARCHAR(32) NOT NULL COMMENT '班级',
customer_id INT NOT NULL UNIQUE,
FOREIGN KEY(customer_id) REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE  -- 外键字段必须是唯一的
)ENGINE=INNODB CHARSET=utf8;

INSERT INTO student(class_name,customer_id) VALUES
('python',1),
('linux',2),
('mysql',3);
see also:

老男孩-标杆班级-MySQL-lesson03-SQL基础应用 | mysql表的完整性约束 | mysql三-3:完整性约束 | MySQL sql_mode 说明（及处理一起 sql_mode 引发的问题） | 5.1.10服务器SQL模式 | mysql三-2:数据类型 | mysql支持的数据类型 | MySQL 数据类型 | Data Types | 11.4.1 The CHAR and VARCHAR Types | C.10.4 Limits on Table Column Count and Row Size | 11.4.1 The CHAR and VARCHAR Types

posted @ 2020-08-28 12:29  听雨危楼  阅读(527)  评论(0)  编辑  收藏  举报