4

On my website, an user can insert a hyperlink in a text input field. Then, this link (a string) is stored in my database.

On MySQL, the field that contains the hyperlink string is TEXT type, but I think its too long for this kind of information. Besides, VARCHAR(255) is too short sometimes.

What's the best type/length to store a hyperlink? It would be nice to know how long a link can be.

AJ.
  • 27,586
  • 18
  • 84
  • 94
markzzz
  • 47,390
  • 120
  • 299
  • 507
  • When a project I was working on was faced with this same problem, we took a two layer approach -- limit to 4K, but normalize the URLs and store the ID of the URL record in the other tables. In our application, URLs were repeated very often, so the normalization made sense for us. – Dilum Ranatunga Apr 22 '11 at 23:58
  • Along the normalization approach, you can opt to store the bit.ly URL for the address... the API is available at http://code.google.com/p/bitly-api/wiki/ApiDocumentation – Dilum Ranatunga Apr 23 '11 at 00:05
  • 1
    The limit to a hyperlink is what makes sense as an URL. Many webservers will happily accept 16kB, but of course such a thing is totally nuts. Decide for yourself what's sane. URLs are meant to be human readable, usable, and memorizable. In my opinion, having URLs longer than 60-80 characters are a nuisance already, but people using URLs upwards of 100-120 characters (Amazon, eh?) are outright insane or simply ignorant. Ask yourself who's going to type in a 200 character string, or imagine spelling a 300 char URL on the phone. So if you use a VARCHAR(255) you are absolutely on the safe side. – Damon Apr 23 '11 at 00:24
  • Yeah I think I'll put varchar(255), and check on server side if the string is long more than 255 (else I'll cut). 255 is enough :) – markzzz Apr 23 '11 at 00:56

3 Answers3

3

There is no limit to how long a hyperlink can be. Browsers limit the amount of data a GET request can send with a hyperlink, but there is no limit on how long the actual link itself can be.

A standard TEXT field will be fine for storing links (you won't suffer a performance hit for using that field as opposed to the VARCHAR(255) field type, nor will you use extra memory, so there's really no reason not to use it.)

BraedenP
  • 7,125
  • 4
  • 33
  • 42
2

HTTP does not specify any limit to the length of URLs. However, webservers and browsers may put a limit.

I guess some old IE versions have a limit of just around 250 characters.

Cracker
  • 1,780
  • 4
  • 24
  • 34
  • Then it was increased to slightly less than 2K (see http://stackoverflow.com/questions/417142/what-is-the-maximum-length-of-a-url ). – Dilum Ranatunga Apr 22 '11 at 23:55
2

See: What is the maximum length of a URL in different browsers?

You could use VARCHAR(2048) if you want a lower limit. There's nothing wrong with using TEXT in terms of space though. In both cases they only use as much space as the text plus a few bytes to represent the length. See Data Type Storage Requirements for details.

As for whether or not you should pick VARCHAR or TEXT see: MySQL: Large VARCHAR vs. TEXT?

Community
  • 1
  • 1
WhiteFang34
  • 70,765
  • 18
  • 106
  • 111
  • Oh...didn't know that Text use as much space as the text. this is false about `int` right? – markzzz Apr 23 '11 at 00:07
  • And, So why exist varchar? Using TEXT will be the same :) – markzzz Apr 23 '11 at 00:11
  • 1
    That's correct, `INT` is always 4 bytes. For `TEXT` it's the length plus 2 bytes. See http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html for details. – WhiteFang34 Apr 23 '11 at 00:13
  • 1
    As for why you might want `VARCHAR` vs `TEXT` see http://stackoverflow.com/questions/2023481/mysql-large-varchar-vs-text – WhiteFang34 Apr 23 '11 at 00:14