如何约束MySQL字段

发布时间：2023-10-26 15:56:39 所属栏目：MySql教程来源：

导读：下文主要给大家带来怎么样约束MySQL字段，希望这些内容能够带给大家实际用处，这也是我编辑怎么样约束MySQL字段这篇文章的主要目的。好了，废话不多说，大家直接看下文吧。

一、字段字段修饰符的使用

1.1 nu

下文主要给大家带来怎么样约束MySQL字段，希望这些内容能够带给大家实际用处，这也是我编辑怎么样约束MySQL字段这篇文章的主要目的。好了，废话不多说，大家直接看下文吧。

一、字段字段修饰符的使用

1.1 null和not null修饰符

null占用空间，此字段不可为空

not unll设置的字段中可以为空，卡插入控制，其插入为空值的时候不占用空间。

例：定义一个表定义字段为null类型和not null进行比较。

mysql> create table myziduan(char1 varchar(18) not null,char2 varchar(18))ENGINE=myisam;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from myziduan;

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

| char1 | char2 |

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

|       | NULL |

| A     | B     |

|       | B     |

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

3 rows in set (0.00 sec)

复制代码

为空字段不占用空间，null是需要占用空间的。

1.2设定表中自定义默认字段-default

MySQL表中如果该字段没有设定default，则MySQL将依据这个字段是nll还是otnull，如果为可以为null则为null。如果不可以为null则报错。

如果时间字段，默认为欸当前时间，插入为0时，默认为当前时间。如果是enum（枚举）类型则默认为第一个元素

例；

mysql> insert into myziduan2(id,name) values(4,'诸葛亮');

Query OK, 1 row affected (0.01 sec)

mysql> select * from myziduan2;

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

| id | name      | depth |

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

| 1 | 张飞      | 110   |

| 2 | 刘备      | NULL |

| 3 | 关羽      |       |

| 4 | 诸葛亮    | SOS   |

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

4 rows in set (0.00 sec)

复制代码

1.3 自增长字段---auto_increment

auto_increment只能用来修饰int字段，表明MySQL应该自动为该字段生成一个唯一没有用过的数(每次在最大ID值的基础上加1，同时对于最大ID值设定为21时，如果此时将该ID删除，再插入id，此时ID将会从22开始)，对于主键，这个是有很大用处的。可以为每条记录创建一个唯一的标识符。

create table myziduan3(uuid int(10) auto_increment primary key,name varchar(48) not null,address varchar(48));rchar(48));

Query OK, 0 rows affected (0.02 sec)

mysql> insert into myziduan3(name,address) values('孙悟空','花果山'),('猪八戒','高家'),('沙悟净','流沙河');

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from myziduan3;

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

| uuid | name      | address   |

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

|    1 | 孙悟空    | 花果山    |

|    2 | 猪八戒    | 高家      |

|    3 | 沙悟净    | 流沙河    |

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

3 rows in set (0.00 sec)

mysql>

mysql> insert into myziduan3(uuid,name,address) values(10,'小白龙','东海');

Query OK, 1 row affected (0.01 sec)

mysql> insert into myziduan3(name,address) values('唐三藏','长安');

Query OK, 1 row affected (0.00 sec)

mysql> select * from myziduan3;

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

| uuid | name      | address   |

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

|    1 | 孙悟空    | 花果山    |

|    2 | 猪八戒    | 高家      |

|    3 | 沙悟净    | 流沙河    |

|   10 | 小白龙    | 东海      |

|   11 | 唐三藏    | 长安      |

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

mysql> delete from myziduan3 where uuid=11;

mysql> insert into myziduan3(name,address) values('如来','西天');

Query OK, 1 row affected (0.00 sec)

mysql> select * from myziduan3;

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

| uuid | name      | address   |

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

|    1 | 孙悟空    | 花果山    |

|    2 | 猪八戒    | 高家      |

|    3 | 沙悟净    | 流沙河    |

|   12 | 如来      | 西天      |

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

4 rows in set (0.00 sec)

复制代码

如何清空自增长字段的大小，并重置uuid的最大值。

truncate清空表记录，并重置auto_increment的设定值为0

mysql> truncate table myziduan3;

Query OK, 0 rows affected (0.03 sec)

mysql> select * from myziduan3;

Empty set (0.00 sec)

mysql> insert into myziduan3(name,address) values('如来','西天');

Query OK, 1 row affected (0.01 sec)

mysql> select * from myziduan3;

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

| uuid | name   | address |

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

|    1 | 如来   | 西天    |

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

1 row in set (0.00 sec)

复制代码

？delete和truncate的区别?

delete和truncate均能删除表中数据，但是delete不会删除关于表中的auto_increment记录，而truncate table name则能重置auto_increment的初始值。

二、索引

索引是一种特殊文件，其作为innoDB数据表上的索引是表空间的一个组成部分。它们包含着对数据表所有记录的引用指针，通俗的说，数据库数索引好似书的目录，能加快数据库的查询速度。

优点：加快检索速度，减少查询时间

缺点：索引是以文件存储，如果索引过多，则磁盘空间较大。二期他影响insert，update，delete的执行时间。

索引创建原则：

2.1 普通索引

mysql> create table suoyin1(id int(5),name varchar(20),pwd varchar(28),index(pwd));

mysql> desc suoyin1;

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

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

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

| id    | int(5)      | YES |     | NULL    |       |

| name | varchar(20) | YES |     | NULL    |       |

| pwd   | varchar(28) | YES | MUL | NULL    |       |

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

3 rows in set (0.01 sec)

复制代码

2.1.1 添加索引-alter

语法：alter table tb_name add index 索引名称 (字段1，字段2...);

mysql> alter table suoyin2 add tel varchar(13);

Query OK, 0 rows affected (0.29 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table suoyin2 add index index_tel (tel);

Query OK, 0 rows affected (0.17 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc suoyin2;

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

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

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

| id    | int(5)      | YES |     | NULL    |       |

| name | varchar(20) | YES |     | NULL    |       |

| pwd   | varchar(28) | YES | MUL | NULL    |       |

| tel   | varchar(13) | YES | MUL | NULL    |       |

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

复制代码

2.1.2 查看索引：

mysql> desc suoyin2;

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

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

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

| id    | int(5)      | YES |     | NULL    |       |

| name | varchar(20) | YES |     | NULL    |       |

| pwd   | varchar(28) | YES | MUL | NULL    |       |

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

3 rows in set (0.00 sec)

复制代码

key类型为MUL表示的是普通索引，允许重复值。

2.1.3 索引删除

mysql> alter table suoyin2 drop key index_tel;

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc suoyin2;

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

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

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

| id    | int(5)      | YES |     | NULL    |       |

| name | varchar(20) | YES |     | NULL    |       |

| pwd   | varchar(28) | YES | MUL | NULL    |       |

| tel   | varchar(13) | YES |     | NULL    |       |

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

4 rows in set (0.00 sec)

复制代码

2.2 唯一索引

唯一索引故名思意，该索引列的值不能重复，只能出现一次，必须唯一，用来约束内容，字段也只能出现一次，用来约束内容，唯一型索引允许有NUL值。

语法：create table tb_name(字段定义:unique key 索引名 (字段))；

注意：常用在值不能重复的字段上，比如用户名，电话号码和×××号等。

mysql> create table suoyin3(uuid int(10) auto_increment primary key,Name varchar(18),Pwd varchar(15),unique index (Name));

mysql> desc suoyin3;

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

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

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

| uuid | int(10)     | NO   | PRI | NULL    | auto_increment |

| Name | varchar(18) | YES | UNI | NULL    |                |

| Pwd   | varchar(15) | YES |     | NULL    |                |

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

复制代码

2.2.1 修改唯一型索引

删除索引：

alter table tb_name drop key key_name;

mysql> alter table suoyin3 drop key Name;

复制代码

添加索引：

alter table tb_name add unique(name);

mysql> alter table suoyin3 add key name (Name);

复制代码

2.3 主键索引：

在查询数据库是，按照主键索引查找速度最快，每个表只能有一个主键列，可以有多个普通索引列，主键列要求列的所有内容必须唯一，而索引列不要求内容必须唯一，不允许为空。

2.3.1 创建主键是索引:

语法：create table tb_name(列定义，)

mysql> create table primary1(uuid int(10) not null auto_increment primary key,name varchar(18) not null);

Query OK, 0 rows affected (0.16 sec)

mysql> desc primary1;

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

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

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

| uuid | int(10)     | NO   | PRI | NULL    | auto_increment |

| name | varchar(18) | NO   |     | NULL    |                |

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

2 rows in set (0.00 sec)

复制代码

2.3.1 删除主键索引键值，必须先修改索引对应字段的修饰符

mysql> alter table primary1 change uuid uuid int(10) not null;

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

复制代码

mysql> alter table primary1 drop primary key;

Query OK, 0 rows affected (0.16 sec)

Records: 0 Duplicates: 0 Warnings: 0

复制代码

添加索引：

mysql> alter table primary1 change uuid uuid int(14) not null primary key;

or

mysql> alter table primary1 change uuid uuid int(10) auto_increment primary key;

复制代码

三、复合索引：

一个表中创建的索引多余2个的时候，则被称作符合索引，

例：创建一个表存放云服务器允许或拒绝的IP和port，要记录中的IP和Port需要唯一。

mysql> create table firewall(host varchar(15) not null,port smallint(4) not null,access enum('deny','allow')not null,primary key(host,port));

Query OK, 0 rows affected (0.13 sec)

复制代码

数据插入效果

mysql> insert into firewall values('192.168.31.101',56,'allow');

Query OK, 1 row affected (0.01 sec)

mysql> insert into firewall values('192.168.31.101',56,'allow');

ERROR 1062 (23000): Duplicate entry '192.168.31.101-56' for key 'PRIMARY'

mysql> insert into firewall values('192.168.31.102',56,'deny');

Query OK, 1 row affected (0.00 sec)

mysql> insert into firewall values('192.168.31.101',80,'deny');

Query OK, 1 row affected (0.01 sec)

mysql> insert into firewall values('192.168.31.102',56,'deny');

ERROR 1062 (23000): Duplicate entry '192.168.31.102-56' for key 'PRIMARY'

复制代码

在插入相同的IP和端口时会报错。

创建表时，加入各种索引的顺序如下：

create table tb_name (字段定义，primarykey ('key字段')，unique key 'BI' ('ukey'),key ('key_word'),key (other));

怎么样约束MySQL字段

四、全文索引

MySQL的全文索引是目前搜索引擎使用的一种关键技术，它能够利用分词技术，等多种算法智能分析处文本中文字中关键字词的频率及重要性，然后按照一定的算法规则智能赛选出搜索结果。

在MySQL 5.7版本之前全文索引只支持MISAM存储引擎的，在之后的版本中InnoDB引擎也引入了全文索引功能。

例如：MySQL在数据量比较大和高并发链接的情况下，

select语句 where bName like '%网%'

其中%在此表示通配符，不通过索引，直接进行权标扫描。

使用全文索引对MySQL数据库的压力比较大。

创建全文索引：

方式一：

create table tb_name(字段定义，fulltext key 索引名(字段))；

方式二：

alter table tb_nameadd fulltext 索引名(字段)；

五、外键约束

外键约束，故名思意就是建立表与表之间设置某种关系，正是由于这种关系的存在，能够使表之间的数据进行关联，并通过使用外键约束使得表与表之间更加完整，使表的关联性更强，也因此保证列表的完整性。

5.1 创建外键语法：

create table tb_name(... [constraint [约束名] foreign key [外键字段]] references [外键表名](外键字段1，外键字段2...)[on

delete cascade][on update cascade]);

注：on update cascade是级联更新操作，on delete cascade是级联删除。也就是在你更新或删除主键表，那外键表也会跟随一起更新和删除。

外键创建成功需要满足以下几点：

1、确保参照的字段和表真实存在

2、组成外键的字段被索引

3、必须使用type指定的存储引擎为innodb；

4、外键字段和关联字段，数据类型必须一致。

例：创建一个用户表和产品表并通过引入外键foreign key使其进行关联；

mysql> create table USER(uid int(10) auto_increment,uname varchar(18) not null,sex ENUM('男','女')default '女',Tel varchar(14),address varchar(60),primary key u_id(uid));

Query OK, 0 rows affected (0.09 sec)

mysql> create table 订单(oid int(10) auto_increment,uid int(10) not null,goods varchar(48) not null,gid int(18) not null,money int(10) not null,primary key o_id(oid),index g_name(goods),foreign key order_f_key(uid) references USER(uid) on delete cascade on update cascade);

Query OK, 0 rows affected (0.18 sec)

插入数据

mysql> insert into USER(uname,sex) values('张飞','男'),('关羽','男'),('小乔','女');

mysql> insert into 订单(oid,uid,goods,gid,money) values(0000001,1,'双汇牛肉',100000001,56),(0000002,3,'邦杰牛肉',100000011,'54');

Query OK, 2 rows affected (0.08 sec)

复制代码

查询：

mysql> select u.uid,u.uname,o.gid,o.goods,o.money from USER as u left join 订单 as o on u.uid=o.uid;

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

| uid | uname | gid       | goods        | money |

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

|   1 | 张飞   | 100000001 | 双汇牛肉     |    56 |

|   3 | 小乔   | 100000011 | 邦杰牛肉     |    54 |

|   2 | 关羽   |      NULL | NULL         | NULL |

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

3 rows in set (0.00 sec)

复制代码

删除测试：

将表'用户'uid=1的用户删除

mysql> delete from table USER where uid=1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table USER where uid=1' at line 1

复制代码

测试更新：

mysql> update USER set uid=6 where uname='张飞';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

复制代码

查询更新结果

mysql> select u.uid,o.gid,o.goods,o.money from USER as u left join 订单 as o on u.uid=o.uid;

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

| uid | gid       | goods        | money |

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

|   6 | 100000001 | 双汇牛肉     |    56 |

|   3 | 100000011 | 邦杰牛肉     |    54 |

|   2 |      NULL | NULL         | NULL |

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

3 rows in set (0.00 sec)

复制代码

以上结果表明外键的引入有效的保存了数据表的完整型。

？？？？创建表之后再创建外键如何操作

mysql> create table order1(oid int(10) auto_increment,uid int(11) default '0',username varchar(18),money int(11),primary key(oid),index(uid));

mysql> alter table order1 add foreign key(uid) references USER(uid) on delete cascade on update cascade;

Query OK, 0 rows affected (0.26 sec)

Records: 0 Duplicates: 0 Warnings: 0

复制代码

自定义外键名：

mysql> alter table order1 add constraint `fk_name` foreign key(uid) references USER(uid) on delete cascade on update cascade;

Query OK, 0 rows affected (0.12 sec)

Records: 0 Duplicates: 0 Warnings: 0

复制代码

删除外键：

mysql> alter table order1 drop foreign key order1_ibfk_1;

复制代码

查看外键：

mysql> show create table 订单\G;

*************************** 1. row ***************************

       Table: 订单

Create Table: CREATE TABLE `订单` (

  `oid` int(10) NOT NULL AUTO_INCREMENT,

  `uid` int(10) NOT NULL,

  `goods` varchar(48) NOT NULL,

  `gid` int(18) NOT NULL,

  `money` int(10) NOT NULL,

  PRIMARY KEY (`oid`),

  KEY `g_name` (`goods`),

  KEY `order_f_key` (`uid`),

  CONSTRAINT `订单_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `USER` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

ERROR:

No query specified

复制代码

mysql>

六、视图：

mysql> create view bc as select b.bName,b.price,c.bTypeName from books as b left join category as c on b.bTypeId=c.bTypeId;

复制代码

查看创建信息

mysql> show create view bc\G

*************************** 1. row ***************************

                View: bc

         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bc` AS select `b`.`bName` AS `bName`,`b`.`price` AS `price`,`c`.`bTypeName` AS `bTypeName` from (`books` `b` left join `category` `c` on((`b`.`bTypeId` = `c`.`bTypeId`)))

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set (0.00 sec)

查看

mysql> select * from bc\G;

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!