HTML/CSS
瀏覽器腳本
服務端
移動端
數據庫
產品運營
設計教程
智能硬件
開發工具
網站建設Mysql添加和刪除唯一
添加唯一
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [索引名稱](字段名稱,...)
添加唯一性約束或索引，測試添加唯一索引
mysql> CREATE TABLE IF NOT EXISTS user12( -> id TINYINT UNSIGNED KEY AUTO_INCREMENT, -> username VARCHAR(20) NOT NULL, -> card CHAR(18) NOT NULL, -> test VARCHAR(20) NOT NULL, -> test1 CHAR(32) NOT NULL -> ); Query OK, 0 rows affected (0.29 sec)
mysql> DESC user12; +----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | | NULL | | | card | char(18) | NO | | NULL | | | test | varchar(20) | NO | | NULL | | | test1 | char(32) | NO | | NULL | | +----------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.03 sec)
選中表text12把username添加唯一約束
mysql> ALTER TABLE user12 ADD UNIQUE(username); Query OK, 0 rows affected (0.38 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user12; +----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | card | char(18) | NO | | NULL | | | test | varchar(20) | NO | | NULL | | | test1 | char(32) | NO | | NULL | | +----------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)
查看表text12 創建時的詳細定義
mysql> SHOW CREATE TABLE user12; +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user12 | CREATE TABLE `user12` ( `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL, `card` char(18) NOT NULL, `test` varchar(20) NOT NULL, `test1` char(32) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
添加的同時加、指定索引名稱。名稱的作用是在刪除是好使用索引名稱。給user12添加索引并寫上名稱
mysql> ALTER TABLE user12 ADD CONSTARINT symbol uni_card UNIQUE KEY(card); 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 'symbol uni_card UNIQUE KEY(card)' at line 1
索引名稱要放在后面
mysql> ALTER TABLE user12 ADD CONSTARINT symbol UNIQUE KEY uni_card(card); 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 'symbol UNIQUE KEY uni_card(card)' at line 1
索引名稱要放在 symbol后面
mysql> ALTER TABLE user12 ADD CONSTRAINT symbol uni_card UNIQUE KEY(card); 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 'uni_card UNIQUE KEY(card)' at line 1
索引名稱要放在最后面
mysql> ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE KEY uni_card(card); Query OK, 0 rows affected (0.24 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user12; +----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | card | char(18) | NO | UNI | NULL | | | test | varchar(20) | NO | | NULL | | | test1 | char(32) | NO | | NULL | | +----------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
查看表text12 創建時的詳細定義
mysql> SHOW CREATE TABLE user12; +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user12 | CREATE TABLE `user12` ( `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL, `card` char(18) NOT NULL, `test` varchar(20) NOT NULL, `test1` char(32) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), UNIQUE KEY `uni_card` (`card`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
添加復合（聯合）索引
mysql> ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test,test1); Query OK, 0 rows affected (0.23 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user12; +----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | card | char(18) | NO | UNI | NULL | | | test | varchar(20) | NO | MUL | NULL | | | test1 | char(32) | NO | | NULL | | +----------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
查看表text12 創建時的詳細定義
mysql> SHOW CREATE TABLE user12; +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user12 | CREATE TABLE `user12` ( `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL, `card` char(18) NOT NULL, `test` varchar(20) NOT NULL, `test1` char(32) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), UNIQUE KEY `uni_card` (`card`), UNIQUE KEY `mulUni_test_test1` (`test`,`test1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
刪除唯一
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name刪除text12 username主鍵
mysql> ALTER TABLE user12 DROP INDEX username; Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user12; +----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | | NULL | | | card | char(18) | NO | UNI | NULL | | | test | varchar(20) | NO | MUL | NULL | | | test1 | char(32) | NO | | NULL | | +----------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)
刪除兩個唯一性索引
mysql> ALTER TABLE user12 DROP KEY uni_card; Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> ALTER TABLE user12 DROP KEY mulUni_test_test1; Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user12; +----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | | NULL | | | card | char(18) | NO | | NULL | | | test | varchar(20) | NO | | NULL | | | test1 | char(32) | NO | | NULL | | +----------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)
查看表text12 創建時的詳細定義
mysql> SHOW CREATE TABLE user12; +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user12 | CREATE TABLE `user12` ( `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL, `card` char(18) NOT NULL, `test` varchar(20) NOT NULL, `test1` char(32) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
【本文由麥子學院獨家原創，轉載請注明出處并保留原文鏈接】
