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?
10 Answers
- 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)
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(max) for SQLServer2005
varchar(65535) for MySQL 5.0.3 and later
This will allocate storage as need and shouldn't affect performance.
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.
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.
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.
Related