sql - Best database field type for a URL - Stack Overflow

 5 years ago
source link: http://stackoverflow.com/questions/219569/best-database-field-type-for-a-url
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Best database field type for a URL

By using our site, you acknowledge that you have read and understand our Cookie Policy, Privacy Policy, and our Terms of Service.

Asked 11 years, 8 months ago
Viewed 236k times

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?

asked Oct 20 '08 at 19:29
  1. Lowest common denominator max URL length among popular web browsers: 2,083 (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.

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

answered Oct 20 '08 at 19:55

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.

answered Oct 20 '08 at 19:31

varchar(max) for SQLServer2005

varchar(65535) for MySQL 5.0.3 and later

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

answered Oct 20 '08 at 19:31

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.

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

  1. You'll use a lot of URLs per query (unlike TEXT columns, VARCHARs are stored inline with the row)
  2. You're pretty sure that a URL will never exceed the row-limit of 65,535 bytes.

Use TEXT if :

  1. The URL really might break the 65,535 byte row limit
  2. 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.
answered May 22 '13 at 22:45

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.

VARCHAR(512) CHARACTER SET 'ascii' COLLATE 'ascii_general_ci' NOT NULL
answered Dec 18 '15 at 10:00

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.

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.

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/1235702 would be displayed as stackoverflow.com/q/21956... and would link to a shortened 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.
answered Sep 8 '14 at 19:33

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.

answered Oct 20 '08 at 19:33

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.

answered Oct 20 '08 at 19:33

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.

answered Oct 20 '08 at 19:50

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.

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.

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

answered Dec 19 '12 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged sql mysql database or ask your own question.

About Joyk

Aggregate valuable and interesting links.
Joyk means Joy of geeK