URL的最佳数据库字段类型

I need to store a url in a MySQL table. What's the best practice for defining a field that will hold a URL with an undetermined length?

我需要在MySQL表中存储一个url。定义一个字段来保存一个未确定长度的URL的最佳实践是什么?

10 个解决方案

#1

1. Lowest common denominator max URL length among popular web browsers: 2,083 (Internet Explorer)

   流行web浏览器中最小公分母最大URL长度:2083 (Internet Explorer)

2. http://dev.mysql.com/doc/refman/5.0/en/char.html
   Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

   VARCHAR列中的http://dev.sqmyl.com/doc/refman/5.0 /en/char.html值是可变长度的字符串。长度可以在MySQL 5.0.3之前指定为0到255，在5.0.3和以后的版本中指定为0到65,535。在MySQL 5.0.3中，VARCHAR的有效最大长度将取决于最大行大小(65,535字节，在所有列之间共享)和使用的字符集。

3. So ...
   < MySQL 5.0.3 use TEXT
   or
   >= MySQL 5.0.3 use VARCHAR(2083)

   所以…< MySQL 5.0.3使用文本或>= MySQL 5.0.3使用VARCHAR(2083)

#2

VARCHAR(512) (or similar) should be sufficient. However, since you don't really know the maximum length of the URLs in question, I might just go direct to TEXT. The danger with this is of course loss of efficiency due to CLOBs being far slower than a simple string datatype like VARCHAR.

VARCHAR(512)(或类似)应该足够。但是，由于您并不真正了解问题中的url的最大长度，所以我可能直接指向文本。这样做的危险当然是由于clob比VARCHAR这样的简单字符串数据类型慢得多，从而降低了效率。

#3

varchar(max) for SQLServer2005

SQLServer2005 varchar(max)

varchar(65535) for MySQL 5.0.3 and later

varchar(65535)适用于MySQL 5.0.3及以后

This will allocate storage as need and shouldn't affect performance.

这将根据需要分配存储，不会影响性能。

#4

You'll want to choose between a TEXT or VARCHAR column based on how often the URL will be used and whether you actually need the length to be unbound.

在文本或VARCHAR列之间，您需要根据URL的使用频率和是否需要取消绑定的长度来选择。

Use VARCHAR with maxlength >= 2,083 as micahwittman suggested if:

使用VARCHAR，最大长度>= 2083，如micahwittman建议:

1. You'll use a lot of URLs per query (unlike TEXT columns, VARCHARs are stored inline with the row)
2. 每个查询将使用许多url(与文本列不同，VARCHARs是与行内联存储的)
3. You're pretty sure that a URL will never exceed the row-limit of 65,535 bytes.
4. 您非常确定URL永远不会超过65,535字节的行限制。

Use TEXT if :

使用文本如果:

1. The URL really might break the 65,535 byte row limit
2. URL确实可能会破坏65,535字节行限制。
3. Your queries won't select or update a bunch of URLs at once (or very often). This is because TEXT columns just hold a pointer inline, and the random accesses involved in retrieving the referenced data can be painful.
4. 您的查询不会一次(或非常频繁地)选择或更新一组url。这是因为文本列只是内联地保存一个指针，而检索引用数据所涉及的随机访问可能会很痛苦。

#5

You should use a VARCHAR with an ASCII character encoding. URLs are percent encoded and international domain names use punycode so ASCII is enough to store them. This will use much less space than UTF8.

您应该使用带有ASCII字符编码的VARCHAR。url是百分比编码的，国际域名使用punycode，所以ASCII足够存储它们。这将比UTF8占用更少的空间。

VARCHAR(512) CHARACTER SET 'ascii' COLLATE 'ascii_general_ci' NOT NULL

#6

Most browsers will let you put very large amounts of data in a URL and thus lots of things end up creating very large URLs so if you are talking about anything more than the domain part of a URL you will need to use a TEXT column since the VARCHAR/CHAR are limited.

大多数浏览器会让你把大量的数据在一个URL,因此很多事情最终创建URL非常大如果你谈论任何超过域一个URL的一部分,您将需要使用一个文本自VARCHAR列/ CHAR是有限的。

#7

This really depends on your use case (see below), but storing as TEXT has performance issues, and a huge VARCHAR sounds like overkill for most cases.

这确实取决于您的用例(请参见下面)，但是作为文本存储存在性能问题，而且对于大多数情况来说，巨大的VARCHAR听起来有点过分了。

My approach: use a generous, but not unreasonably large VARCHAR length, such as VARCHAR(500) or so, and encourage the users who need a larger URL to use a URL shortener such as safe.mn.

我的方法是:使用一个慷慨的，但不是不合理的大型VARCHAR长度，比如VARCHAR(500)，并鼓励需要一个较大URL的用户使用一个URL缩短器，例如safe.mn。

The Twitter approach: For a really nice UX, provide an automatic URL shortener for overly-long URL's and store the "display version" of the link as a snippet of the URL with ellipses at the end. (Example: http://*.com/q/219569/1235702 would be displayed as *.com/q/21956... and would link to a shortened URL http://ex.ampl/e1234)

Twitter方法:对于一个非常好的UX，为超长URL提供一个自动的URL缩短器，并将链接的“显示版本”存储为URL的一个片段，最后使用省略号。(示例:http://*.com/q/219569/1235702将显示为*.com/q/21956.02 .)并将链接到缩短的URL http://ex.ampl/e1234)

Notes and Caveats

笔记和警告

• Obviously, the Twitter approach is nicer, but for my app's needs, recommending a URL shortener was sufficient.
• 显然，Twitter的方式更好，但是根据我的应用程序的需要，推荐一个URL缩短器就足够了。
• URL shorteners have their drawbacks, such as security concerns. In my case, it's not a huge risk because the URL's are not public and not heavily used; however, this obviously won't work for everyone. safe.mn appears to block a lot of spam and phishing URL's, but I would still recommend caution.
• URL缩短器有其缺点，比如安全性问题。在我的例子中，这不是很大的风险因为URL不是公共的，也没有被大量使用;然而，这显然并不适用于所有人。安全的。mn似乎阻止了大量的垃圾邮件和网络钓鱼，但我还是建议谨慎。
• Be sure to note that you shouldn't force your users to use a URL shortener. For most cases (at least for my app's needs), 500 characters is overly sufficient for what most users will be using it for. Only use/recommend a URL shortener for overly-long links.
• 请务必注意，您不应该强迫用户使用URL缩短器。在大多数情况下(至少对于我的应用程序的需求来说)，500个字符对于大多数用户来说已经足够了。仅对超长链接使用/推荐URL缩短器。

#8

I don't know about other browsers, but IE7 has a 2083 character limit for HTTP GET operations. Unless any other browsers have lower limits, I don't see why you'd need any more characters than 2083.

我不知道其他浏览器，但是IE7对HTTP GET操作有2083个字符限制。除非其他浏览器有更低的限制，否则我看不出为什么需要超过2083个字符。

#9

You better use varchar(max) which (in terms of size) means varchar (65535). This will even store your bigger web addresses and will save your space as well.

你最好使用varchar(max)，它(就尺寸而言)意味着varchar(65535)。这甚至可以存储更大的web地址，也可以节省空间。

The max specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data.

最大说明符扩展了varchar、nvarchar和varbinary数据类型的存储能力。varchar(max)、nvarchar(max)和varbinary(max)统称为大值数据类型。您可以使用大额数据类型存储2 ^还有字节的数据。

See this article on TechNet about using Using Large-Value Data Types

请参阅TechNet上关于使用大值数据类型的文章

#10

Most web servers have a URL length limit (which is why there is an error code for "URI too long"), meaning there is a practical upper size. Find the default length limit for the most popular web servers, and use the largest of them as the field's maximum size; it should be more than enough.

大多数web服务器都有URL长度限制(这就是为什么“URI太长”有错误代码)，这意味着有一个实用的上限。找到最流行的web服务器的默认长度限制，并使用其中最大的一个作为字段的最大大小;这应该足够了。