mysql表的字段怎么设置唯一性

2025-11-11 05:46:31

1、登录mysql，在test数据库中新建一个表，主键是id，后面把chengshi字段设置为唯一性：

CREATE TABLE `chengshi` ( `id` int(11) NOT NULL AUTO_INCREMENT, `shengfen` varchar(10) DEFAULT NULL, `chengshi` varchar(10) DEFAULT NULL, `shijian` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

mysql表的字段怎么设置唯一性

2、在表中增加一些数据：

insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('1','河南','安阳','2019-03-05 10:14:27');

insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('2','河南','郑州','2019-03-05 10:14:27');

insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('3','河南','洛阳','2019-03-05 10:14:27');

insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('4','河南','漯河','2019-03-05 10:14:27');

insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('5','河南','新乡','2019-03-05 10:14:27');

insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('6','河南','濮阳','2019-03-05 10:14:27');

insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('7','河南','焦作','2019-03-05 10:14:27');

insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('8','河南','鹤壁','2019-03-05 10:14:27');

insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('9','河南','开封','2019-03-05 10:14:27');

mysql表的字段怎么设置唯一性

3、把chengshi字段设置为唯一性：

ALTER TABLE `test`.`chengshi` ADD UNIQUE INDEX `chengshi` (`chengshi`);

mysql表的字段怎么设置唯一性

4、查询chengshi表的数据：

SELECT * FROM chengshi;

mysql表的字段怎么设置唯一性

5、把id=1的城市修改为郑州，出错有重复：

UPDATE chengshi SET chengshi='郑州' WHERE id=1;

mysql表的字段怎么设置唯一性

6、增加一个城市安阳，出现重复：

INSERT INTO chengshi(shengfen,chengshi)VALUES('河南','安阳');

mysql表的字段怎么设置唯一性

声明：本网站引用、摘录或转载内容仅供网站访问者交流或参考，不代表本站立场，如存在版权或非法内容，请联系站长删除，联系邮箱：site.kefu@qq.com。