无法在 MySQL 数据库中创建数据类型为 TEXT 的列

发布于 2024-08-06 19:18:45 字数 931 浏览 8 评论 0原文

我正在创建一个表来保存来自 rss feed 的项目,并且我需要创建一个“描述”列。我选择数据类型为 TEXT,没有设置限制,该列上没有索引。这是我收到的错误:

#1071 - Specified key was too long; max key length is 1000 bytes

如果我选择为此列建立索引,我会收到此错误消息:

#1170 - BLOB/TEXT column 'description' used in key specification without a key length

我为此列指定的任何长度都会返回我收到的第一个错误。有人可以告诉我我做错了什么吗?谢谢!

更新:

我正在使用 MySQL 5.0.4

这是我用来创建表的查询:

CREATE TABLE  `feed_items` (

`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`feed_id` INT NOT NULL COMMENT  '`feeds`.`id`',
`guid` VARCHAR( 255 ) NOT NULL ,
`publish_date` DATETIME NOT NULL ,
`update_of` INT NULL COMMENT  '`feed_items`.`id`',
`link` VARCHAR( 255 ) NOT NULL ,
`title` VARCHAR( 255 ) NOT NULL ,
`description` TEXT NOT NULL ,
`comments_link` VARCHAR( 255 ) NULL ,
INDEX (  `feed_id` ,  `guid` ,  `publish_date` ,  `update_of` ,  `title` )
) ENGINE = MYISAM

I'm creating a table to hold items from rss feeds and I need to create a column 'description'. I selected the datatype as TEXT with no limit set with no index on this column. This is the error I'm getting:

#1071 - Specified key was too long; max key length is 1000 bytes

If I select to index this column, I get this error message:

#1170 - BLOB/TEXT column 'description' used in key specification without a key length

Any length I specify for this column returns the first error I got. Can someone tell me what I'm doing wrong? Thanks!

UPDATE:

I'm using MySQL 5.0.4

Here is the query I'm using to create the table:

CREATE TABLE  `feed_items` (

`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`feed_id` INT NOT NULL COMMENT  '`feeds`.`id`',
`guid` VARCHAR( 255 ) NOT NULL ,
`publish_date` DATETIME NOT NULL ,
`update_of` INT NULL COMMENT  '`feed_items`.`id`',
`link` VARCHAR( 255 ) NOT NULL ,
`title` VARCHAR( 255 ) NOT NULL ,
`description` TEXT NOT NULL ,
`comments_link` VARCHAR( 255 ) NULL ,
INDEX (  `feed_id` ,  `guid` ,  `publish_date` ,  `update_of` ,  `title` )
) ENGINE = MYISAM

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

前事休说 2024-08-13 19:18:45

尽管已经有一段时间了,但这个错误报告似乎正是您遇到的问题:
http://bugs.mysql.com/bug.php?id=4541

您可能需要阅读有关此错误的注释,看看它们是否涉及您的情况,因为如果不知道您使用什么命令来获得此错误,也不知道哪个版本的 mysql,则很难提供帮助。

更新:根据您的查询,您可能需要首先删除它:

INDEX (  `feed_id` ,  `guid` ,  `publish_date` ,  `update_of` ,  `title` )

然后,尝试执行以下操作:

INDEX (  `feed_id`)

当我看到这样的长索引时,我会感到怀疑。

理想情况下,您应该对您将要执行的选择建立索引,因此除非您有,

SELECT * FROM table WHERE feed_id=? AND guid=? AND publish_date=? AND update_of=? and title=?

否则该索引是无用的。由于 feed_id 是一个主键,您应该这样做,

SELECT * FROM table where feed_id=?

因为它将唯一地返回一行。

所以我上面的索引就是所需要的。您可能还需要标题和发布日期的单独索引。

查看您正在编写的查询,然后您可以确定索引应该在哪里。当您添加更多行时,索引会变得昂贵,因此您不想索引太多,但索引太少是没有用的,所以这就是为什么我建议您查看查询,然后决定在哪里建立索引。

Even though it was from a while, this bug report seems to be what you are having an issue with:
http://bugs.mysql.com/bug.php?id=4541

You may want to read through the comments on this bug, and see if they are referring to your situation, since it is hard to help without seeing what command you used to get this error, nor knowing which version of mysql.

UPDATE: Based on your query, you may want to remove this first:

INDEX (  `feed_id` ,  `guid` ,  `publish_date` ,  `update_of` ,  `title` )

Then, try doing:

INDEX (  `feed_id`)

When I see a long index like this I get suspicious.

Ideally you should index what you will be doing selects on, so unless you have

SELECT * FROM table WHERE feed_id=? AND guid=? AND publish_date=? AND update_of=? and title=?

then this index is useless. Since feed_id is a primary key you should just do

SELECT * FROM table where feed_id=?

as it will uniquely return one row.

So the index I have above would be what is needed. You may also want a separate index on title and perhaps publish_date.

Look at what queries you are writing, then you can determine where indexes should be. Indexes will get expensive as you add more rows, so you don't want to index too much, but indexing too little is useless, so that is why I suggest you look at the queries then decide where to have indexes.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文