URL的数据类型 [英] Datatype for URL

问题描述

我阅读到URL的最大长度可以为2,000个字符.因此，我有一个具有varchar(2000)列类型的表来存储URL.但是此列不能仅索引前1000个字符，如下所示.推荐的URL数据类型是什么?

I read that the max length of URL can be 2,000 characters. I have therefore a table with varchar(2000) column type to store URLs. But this column can not be indexing only the first 1000 characters as shown below. What is the recommended datatype for URL?

mysql> create table myweb(id int not null auto_increment, url varchar(2000), primary key (id));
Query OK, 0 rows affected (0.03 sec)

mysql> alter table myweb add key (url);
Query OK, 1 row affected, 1 warning (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table myweb\G
*************************** 1. row ***************************
       Table: myweb
Create Table: CREATE TABLE `myweb` (
  `id` int(11) NOT NULL auto_increment,
  `url` varchar(2000) default NULL,
  PRIMARY KEY  (`id`),
  KEY `url` (`url`(1000))
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

推荐答案

您的问题有很多想像力.

Your question leaves a lot to the imagination.

一方面，我们必须假设您索引的目的是用作避免重复的主键.您将不会开发一个向用户说抱歉，您的1800个字符的数据输入有误；不匹配，请重试."的应用程序.

For one thing we must assume your index's purpose is to serve as a primary key to avoid duplicates. You won't be developing an application that ever says to a user, "sorry, there's a mistake in your 1800-character data entry; it doesn't match, please try again."

另一方面，我们必须假定您的这些URL中可能包含很多CGI参数(?param = val& param = val& param = val).

For another thing, we must assume these URLs of yours potentially have lots of CGI parameters (?param=val&param=val&param=val) in them.

如果这些假设是正确的，那么您可以执行以下操作.

If these assumptions are true, then here's what you can do.

1. 将您的URL列更长一些，作为varchar， 如果你需要.

1. Make your URL column longer, as a varchar, if you need to.

添加SHA-1哈希 列到您的表格. SHA-1哈希 由40个字符的字符串组成 (十六进制).

Add a SHA-1 hash column to your table. SHA-1 hashes consist of strings of 40 characters (hexdigits).

将该列设为您的 首要的关键.

Make that column your primary key.

当您将东西放入 您的表，使用mySQL SHA1 函数来计算哈希值.

When you put stuff into your table, use the mySQL SHA1 function to compute the hash values.

使用INSERT ... ON DUPLICATE KEY UPDATE mySQL命令将行添加到数据库中.

Use the INSERT ... ON DUPLICATE KEY UPDATE mySQL command to add rows to your database.

这将使您可以将重复的URL保留在数据库之外，而不会造成混乱，并且可以很好地进行扩展.

This will let you keep duplicate URLs out of your data base without confusion in a way that scales up nicely.

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

这篇关于URL的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！