如何使mysql字段唯一? [英] How to make a mysql field unique?

问题描述

有没有办法使现有的txt字段唯一(不接受重复的值)?

Is there a way to make an existing txt field unique (do not accept duplicated values)?

字段:post_title
类型:文字
归类:utf8_unicode_ci
空:Sim
默认值:NULL

Field: post_title
Type: text
Collation: utf8_unicode_ci
Null: Sim
Default: NULL

如果有人尝试插入具有现有标题的帖子，会发生什么?

What gonna happens if someone tries to insert a post with an existing title?

这会影响我网站的某些功能吗?

Could that affect some functionality of my site?

结构

CREATE TABLE IF NOT EXISTS `hotaru_posts` (
  `post_id` int(20) NOT NULL AUTO_INCREMENT,
  `post_archived` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `post_updatedts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `post_author` int(20) NOT NULL DEFAULT '0',
  `post_date` timestamp NULL DEFAULT NULL,
  `post_pub_date` timestamp NULL DEFAULT NULL,
  `post_status` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'processing',
  `post_type` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `post_category` int(20) NOT NULL DEFAULT '1',
  `post_tags` text COLLATE utf8_unicode_ci,
  `post_title` text COLLATE utf8_unicode_ci,
  `post_orig_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `post_domain` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `post_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `post_content` text COLLATE utf8_unicode_ci,
  `post_votes_up` smallint(11) NOT NULL DEFAULT '0',
  `post_votes_down` smallint(11) NOT NULL DEFAULT '0',
  `post_comments` enum('open','closed') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'open',
  `post_media` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'text',
  `post_img` text COLLATE utf8_unicode_ci NOT NULL,
  `post_subscribe` tinyint(1) NOT NULL DEFAULT '0',
  `post_updateby` int(20) NOT NULL DEFAULT '0',
  `post_views` int(20) NOT NULL DEFAULT '0',
  `post_last_viewer_ip` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '111.111.111.111',
  PRIMARY KEY (`post_id`),
  KEY `post_archived` (`post_archived`),
  KEY `post_status` (`post_status`),
  KEY `post_type` (`post_type`),
  FULLTEXT KEY `post_title` (`post_title`,`post_domain`,`post_url`,`post_content`,`post_tags`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Story Posts' AUTO_INCREMENT=38275 ;

推荐答案

此处会发生错误，因为MySQL只能索引BLOB或TEXT列的前N个字符.因此，该错误主要发生在尝试将TEXT/BLOB的字段/列类型或属于TEXT或BLOB类型的字段/列类型(例如TINYBLOB，MEDIUMBLOB，LONGBLOB，TINYTEXT，MEDIUMTEXT和LONGTEXT)用作主键或索引时.对于没有长度值的完整BLOB或TEXT，MySQL无法保证列的唯一性，因为它具有可变大小和动态大小.因此，当使用BLOB或TEXT类型作为索引时，必须提供N的值，以便MySQL可以确定密钥长度.但是，MySQL不支持TEXT或BLOB的限制.TEXT(88)根本行不通.

here error happens because MySQL can index only the first N chars of a BLOB or TEXT column. So The error mainly happen when there is a field/column type of TEXT or BLOB or those belongs to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try to make as primary key or index. With full BLOB or TEXT without the length value, MySQL is unable to guarantee the uniqueness of the column as it’s of variable and dynamic size. So, when using BLOB or TEXT types as index, the value of N must be supplied so that MySQL can determine the key length. However, MySQL doesn’t support limit on TEXT or BLOB. TEXT(88) simply won’t work.

因此解决方案是删除TEXT并设置为长度为255的VARCHAR数据类型(默认情况下为长度).

So the solution is remove TEXT and set to VARCHAR datatype with lenght of 255.(BY default lenght).

`post_title` varchar(255) COLLATE utf8_unicode_ci UNIQUE KEY

这篇关于如何使mysql字段唯一?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！