MySQL URL 的最佳数据库字段类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/219569/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best database field type for a URL
提问by Jesse Hattabaugh
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 的字段的最佳实践是什么？
采纳答案by micahwittman
Lowest common denominator max URL length among popular web browsers: 2,083(Internet Explorer)
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.So ...
< MySQL 5.0.3 use TEXT
or
>= MySQL 5.0.3 use VARCHAR(2083)
http://dev.mysql.com/doc/refman/5.0/en/char.html
VARCHAR 列中的值是可变长度的字符串。在 MySQL 5.0.3 之前，长度可以指定为 0 到 255 之间的值，在 5.0.3 及更高版本中可以指定为 0 到 65,535。MySQL 5.0.3 及更高版本中 VARCHAR 的有效最大长度受最大行大小（65,535 字节，在所有列之间共享）和使用的字符集的约束。所以...
< MySQL 5.0.3 use TEXT
or
>= MySQL 5.0.3 use VARCHAR(2083)
回答by Daniel Spiewak
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 的最大长度，我可能会直接转到TEXT. 这样做的危险当然是效率损失，因为CLOBs 比简单的字符串数据类型（如VARCHAR.
回答by Bob Probst
varchar(max)for SQLServer2005
varchar(max)对于 SQLServer2005
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.
这将根据需要分配存储，不应影响性能。
回答by mrgrieves
You'll want to choose between a TEXT or VARCHAR column based on how often the URL will be usedand whether you actuallyneed the length to be unbound.
您需要根据URL 的使用频率以及您是否确实需要取消绑定长度，在TEXT 或 VARCHAR 列之间进行选择。
Use VARCHARwith maxlength >= 2,083as micahwittmansuggested if:
使用最大长度>= 2,083 的VARCHAR作为micahwittman 的建议，如果：
- You'll use a lot of URLs per query (unlike TEXT columns, VARCHARs are stored inline with the row)
- You're pretty sure that a URL will never exceed the row-limit of 65,535 bytes.
- 您将在每个查询中使用大量 URL（与 TEXT 列不同，VARCHAR 存储在行内）
- 您非常确定 URL 永远不会超过 65,535 字节的行限制。
Use TEXTif :
在以下情况下使用文本：
- The URL really might break the 65,535 byte row limit
- 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.
- URL 真的可能会打破 65,535 字节的行限制
- 您的查询不会一次（或经常）选择或更新一堆 URL。这是因为 TEXT 列只包含一个内联指针，而检索引用数据所涉及的随机访问可能会很痛苦。
回答by Flavio Tordini
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
回答by brokethebuildagain
This really depends on your use case (see below), but storing as TEXThas performance issues, and a huge VARCHARsounds like overkill for most cases.
这实际上取决于您的用例（见下文），但存储为TEXT具有性能问题，并且VARCHAR在大多数情况下听起来像是矫枉过正。
My approach:use a generous, but not unreasonably large VARCHARlength, 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)orso，并鼓励需要更大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://stackoverflow.com/q/219569/1235702would be displayed as stackoverflow.com/q/21956...and would link to a shortened URL http://ex.ampl/e1234)
Twitter 方法：对于一个非常好的用户体验，为过长的 URL 提供一个自动 URL 缩短器，并将链接的“显示版本”存储为 URL 的片段，末尾带有省略号。（例如：http://stackoverflow.com/q/219569/1235702将显示为stackoverflow.com/q/21956...并链接到缩短的 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.
- 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.
- 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.
- 显然，Twitter 方法更好，但对于我的应用程序的需求，推荐一个 URL 缩短器就足够了。
- URL 缩短器有其缺点，例如安全问题。就我而言，这不是一个巨大的风险，因为 URL 不是公开的，也没有被大量使用；然而，这显然不适用于每个人。safe.mn 似乎阻止了很多垃圾邮件和网络钓鱼 URL，但我仍然建议谨慎。
- 请务必注意，您不应强迫您的用户使用 URL 缩短器。对于大多数情况（至少对于我的应用程序的需求），500 个字符对于大多数用户将使用它的目的来说已经足够了。仅对过长的链接使用/推荐 URL 缩短器。
回答by carson
Most browsers will let you put very large amounts of data in a URLand 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 的域部分，您将需要使用 TEXT 列，因为VARCHAR/CHAR 是有限的。
回答by matt b
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 多的字符。
回答by CesarB
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.
大多数网络服务器都有一个 URL 长度限制（这就是“URI 太长”的错误代码的原因），这意味着有一个实际的上限。找到最流行的 web 服务器的默认长度限制，并使用其中最大的作为字段的最大大小；它应该绰绰有余。
回答by sohaiby
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). 这甚至可以存储您更大的网址，也可以节省您的空间。
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.
max 说明符扩展了 varchar、nvarchar 和 varbinary 数据类型的存储能力。varchar(max)、nvarchar(max) 和 varbinary(max) 统称为大值数据类型。您可以使用大值数据类型存储最多 2^31-1 字节的数据。
See this articleon TechNet about using Using Large-Value Data Types
请参阅TechNet 上有关使用大值数据类型的这篇文章
