怎么樣約束MySQL字段

下文主要給大家帶來怎么樣約束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 普通索引

最基本的索引，不具備唯一性，能加快查詢速度。

語法：

create table tb_name(字段定義 ，index 索引名稱 (字段)，index 索引名稱(字段))；

注意:在創建索引的時候，可以使用key也可以使用index，其index 索引名稱 (字段)，索引名稱不是必加項，不加的話使用字段作為索引名。

例：創建表時，創建一個普通索引

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 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;

對于以上關于怎么樣約束MySQL字段，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。