Best database field type for a URL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

varchar max  for SQLServer2005  varchar 65535  for MySQL 5 0 3 and later  This will allocate storage as need and shouldn t affect performance

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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      lt  MySQL 5 0 3 use TEXT or  gt   MySQL 5 0 3 use VARCHAR 2083

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    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    Use TEXT if     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

User · Answer

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      lt  MySQL 5 0 3 use TEXT or  gt   MySQL 5 0 3 use VARCHAR 2083

User · Answer

varchar max  for SQLServer2005  varchar 65535  for MySQL 5 0 3 and later  This will allocate storage as need and shouldn t affect performance

User · Answer

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

User · Answer

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

User · Answer

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      lt  MySQL 5 0 3 use TEXT or  gt   MySQL 5 0 3 use VARCHAR 2083

User · Answer

varchar max  for SQLServer2005  varchar 65535  for MySQL 5 0 3 and later  This will allocate storage as need and shouldn t affect performance

User · Answer

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

User · Answer

varchar max  for SQLServer2005  varchar 65535  for MySQL 5 0 3 and later  This will allocate storage as need and shouldn t affect performance

User · Answer

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

User · Answer

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

User · Answer

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    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    Use TEXT if     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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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      lt  MySQL 5 0 3 use TEXT or  gt   MySQL 5 0 3 use VARCHAR 2083

[sql] Best database field type for a URL

Examples related to sql

Examples related to mysql

Examples related to database