Common MySQL fields and their appropriate data types

Question

I am setting up a very small MySQL database that stores  first name  last name  email and phone number and am struggling to find the  perfect  datatype for each field  I know there is no such thing as a perfect answer  but there must be some sort of common convention for commonly used fields such as these  For instance  I have determined that an unformatted US phone number is too big to be stored as an unsigned int  it must be at least a bigint   Because I am sure other people would probably find this useful  I dont want to restrict my question to just the fields I mentioned above   What datatypes are appropriate for common database fields  Fields like phone number  email and address

User · Answer

Any Table ID Use  INT 11   MySQL indexes will be able to parse through an int list fastest  Anything Security Use  BINARY x   or BLOB x   You can store security tokens  etc   as hex directly in BINARY x  or BLOB x    To retrieve from binary-type  use SELECT HEX field     or SELECT      WHERE field   UNHEX  quot ABCD     quot    Anything Date Use  DATETIME  DATE  or TIME  Always use DATETIME if you need to store both date and time  instead of a pair of fields   as a DATETIME indexing is more amenable to date-comparisons in MySQL  Anything True-False Use  BIT 1   MySQL 8-only    Otherwise  use BOOLEAN 1   BOOLEAN is actually just an alias of TINYINT 1   which actually stores 0 to 255  not exactly a true false  is it    Anything You Want to call  SUM      MAX     or similar functions on Use  INT 11   VARCHAR or other types of fields won t work with the SUM    etc   functions  Anything Over 1 000 Characters Use  TEXT  Max limit is 65 535  Anything Over 65 535 Characters Use  MEDIUMTEXT  Max limit is 16 777 215  Anything Over 16 777 215 Characters Use  LONGTEXT  Max limit is 4 294 967 295  FirstName  LastName Use   VARCHAR 255   UTF-8 characters can take up three characters per visible character  and some cultures do not distinguish firstname and lastname   Additionally  cultures may have disagreements about which name is first and which name is last   You should name these fields Person GivenName and Person FamilyName  Email Address Use   VARCHAR 256   The definition of an e-mail path is set in RFC821 in 1982   The maximum limit of an e-mail was set by RFC2821 in 2001  and these limits were kept unchanged by RFC5321 in 2008    See the section  4 5 3 1   Size Limits and Minimums    RFC3696  published 2004  mistakenly cites the email address limit as 320 characters  but this was an  quot info-only quot  RFC that explicitly  quot defines no standards quot  according to its intro  so disregard it  Phone Use  VARCHAR 255   You never know when the phone number will be in the form of  quot 1800    quot   or  quot 1-800 quot   or  quot 1- 800  quot   or if it will end with  quot ext  42 quot   or  quot ask for susan quot   ZipCode Use  VARCHAR 10   You ll get data like 12345 or 12345-6789   Use validation to cleanse this input  URL Use  VARCHAR 2000   Official standards support URL s much longer than this  but few modern browsers support URL s over 2 000 characters   See this SO answer  What is the maximum length of a URL in different browsers  Price Use  DECIMAL 11 2   It goes up to 11

User · Answer

In my experience  first name last name fields should be at least 48 characters -- there are names from some countries such as Malaysia or India that are very long in their full form   Phone numbers and postcodes you should always treat as text  not numbers  The normal reason given is that there are postcodes that begin with 0  and in some countries  phone numbers can also begin with 0  But the real reason is that they aren t numbers -- they re identifiers that happen to be made up of numerical digits  and that s ignoring countries like Canada that have letters in their postcodes   So store them in a text field   In MySQL you can use VARCHAR fields for this type of information  Whilst it sounds lazy  it means you don t have to be too concerned about the right minimum size

User · Answer

In my experience  first name last name fields should be at least 48 characters -- there are names from some countries such as Malaysia or India that are very long in their full form   Phone numbers and postcodes you should always treat as text  not numbers  The normal reason given is that there are postcodes that begin with 0  and in some countries  phone numbers can also begin with 0  But the real reason is that they aren t numbers -- they re identifiers that happen to be made up of numerical digits  and that s ignoring countries like Canada that have letters in their postcodes   So store them in a text field   In MySQL you can use VARCHAR fields for this type of information  Whilst it sounds lazy  it means you don t have to be too concerned about the right minimum size

User · Answer

Here are some common datatypes I use  I am not much of a pro though      Column             Data type       Note   ----------------   -------------   -------------------------------------   id                 INTEGER         AUTO INCREMENT  UNSIGNED                                                                uuid               CHAR 36         or CHAR 16  binary                                                                      title              VARCHAR 255                                                                                             full name          VARCHAR 70                                                                                              gender             TINYINT         UNSIGNED                                                                                description        TINYTEXT        often may not be enough  use TEXT                                       instead             post body          TEXT                                                                                                    email              VARCHAR 255                                                                                             url                VARCHAR 2083    MySQL version  lt  5 0 3 - use TEXT                                                        salt               CHAR x          randomly generated string  usually of                                       fixed length  x        digest  md5        CHAR 32                                                                                                 phone number       VARCHAR 20                                                                                              US zip code        CHAR 5          Use CHAR 10  if you store extended                                       codes         US Canada p code   CHAR 6                                                                                                  file path          VARCHAR 255                                                                                             5-star rating      DECIMAL 3 2     UNSIGNED                                                                                price              DECIMAL 10 2    UNSIGNED                                                                                date  creation     DATE DATETIME   usually displayed as initial date of                                       a post                                             date  tracking     TIMESTAMP       can be used for tracking changes in a                                       post                                              tags  categories   TINYTEXT        comma separated values                                                                  status             TINYINT 1       1     published  0     unpublished      You                                       can also use ENUM for human-readable                                       values   json data          JSON            or LONGTEXT

User · Answer

Since you re going to be dealing with data of a variable length  names  email addresses   then you d be wanting to use VARCHAR  The amount of space taken up by a VARCHAR field is  field length    1 bytes  up to max length 255  so I wouldn t worry too much about trying to find a perfect size  Take a look at what you d imagine might be the longest length might be  then double it and set that as your VARCHAR limit  That said      I generally set email fields to be VARCHAR 100  - i haven t come up with a problem from that yet  Names I set to VARCHAR 50    As the others have said  phone numbers and zip postal codes are not actually numeric values  they re strings containing the digits 0-9  and sometimes more    and therefore you should treat them as a string  VARCHAR 20  should be well sufficient   Note that if you were to store phone numbers as integers  many systems will assume that a number starting with 0 is an octal  base 8  number  Therefore  the perfectly valid phone number  0731602412  would get put into your database as the decimal number  124192010

User · Answer

Here are some common datatypes I use  I am not much of a pro though      Column             Data type       Note   ----------------   -------------   -------------------------------------   id                 INTEGER         AUTO INCREMENT  UNSIGNED                                                                uuid               CHAR 36         or CHAR 16  binary                                                                      title              VARCHAR 255                                                                                             full name          VARCHAR 70                                                                                              gender             TINYINT         UNSIGNED                                                                                description        TINYTEXT        often may not be enough  use TEXT                                       instead             post body          TEXT                                                                                                    email              VARCHAR 255                                                                                             url                VARCHAR 2083    MySQL version  lt  5 0 3 - use TEXT                                                        salt               CHAR x          randomly generated string  usually of                                       fixed length  x        digest  md5        CHAR 32                                                                                                 phone number       VARCHAR 20                                                                                              US zip code        CHAR 5          Use CHAR 10  if you store extended                                       codes         US Canada p code   CHAR 6                                                                                                  file path          VARCHAR 255                                                                                             5-star rating      DECIMAL 3 2     UNSIGNED                                                                                price              DECIMAL 10 2    UNSIGNED                                                                                date  creation     DATE DATETIME   usually displayed as initial date of                                       a post                                             date  tracking     TIMESTAMP       can be used for tracking changes in a                                       post                                              tags  categories   TINYTEXT        comma separated values                                                                  status             TINYINT 1       1     published  0     unpublished      You                                       can also use ENUM for human-readable                                       values   json data          JSON            or LONGTEXT

User · Answer

Someone s going to post a much better answer than this  but just wanted to make the point that personally I would never store a phone number in any kind of integer field  mainly because    You don t need to do any kind of arithmetic with it  and Sooner or later someone s going to try to  do something like  put brackets around their area code    In general though  I seem to almost exclusively use    INT 11  for anything that is either an ID or references another ID DATETIME for time stamps VARCHAR 255  for anything guaranteed to be under 255 characters  page titles  names  etc  TEXT for pretty much everything else    Of course there are exceptions  but I find that covers most eventualities

User · Answer

Someone s going to post a much better answer than this  but just wanted to make the point that personally I would never store a phone number in any kind of integer field  mainly because    You don t need to do any kind of arithmetic with it  and Sooner or later someone s going to try to  do something like  put brackets around their area code    In general though  I seem to almost exclusively use    INT 11  for anything that is either an ID or references another ID DATETIME for time stamps VARCHAR 255  for anything guaranteed to be under 255 characters  page titles  names  etc  TEXT for pretty much everything else    Of course there are exceptions  but I find that covers most eventualities

User · Answer

I am doing about the same thing  and here s what I did   I used separate tables for name  address  email  and numbers  each with a NameID column that is a foreign key on everything except the Name table  on which it is the primary clustered key   I used MainName and FirstName instead of LastName and FirstName to allow for business entries as well as personal entries  but you may not have a need for that   The NameID column gets to be a smallint in all the tables because I m fairly certain I won t make more than 32000 entries   Almost everything else is varchar n  ranging from 20 to 200  depending on what you wanna store  Birthdays  comments  emails  really long names    That is really dependent on what kind of stuff you re storing   The Numbers table is where I deviate from that   I set it up to have five columns labeled NameID  Phone   CountryCode  Extension  and PhoneType   I already discussed NameID   Phone  is varchar 12  with a check constraint looking something like this   CHECK  Phone  like   0-9  0-9  0-9 - 0-9  0-9  0-9 - 0-9  0-9  0-9  0-9      This ensures that only what I want makes it into the database and the data stays very consistent   The extension and country codes I called nullable smallints  but those could be varchar if you wanted to   PhoneType is varchar 20  and is not nullable   Hope this helps

User · Answer

Since you re going to be dealing with data of a variable length  names  email addresses   then you d be wanting to use VARCHAR  The amount of space taken up by a VARCHAR field is  field length    1 bytes  up to max length 255  so I wouldn t worry too much about trying to find a perfect size  Take a look at what you d imagine might be the longest length might be  then double it and set that as your VARCHAR limit  That said      I generally set email fields to be VARCHAR 100  - i haven t come up with a problem from that yet  Names I set to VARCHAR 50    As the others have said  phone numbers and zip postal codes are not actually numeric values  they re strings containing the digits 0-9  and sometimes more    and therefore you should treat them as a string  VARCHAR 20  should be well sufficient   Note that if you were to store phone numbers as integers  many systems will assume that a number starting with 0 is an octal  base 8  number  Therefore  the perfectly valid phone number  0731602412  would get put into your database as the decimal number  124192010

User · Answer

Any Table ID Use  INT 11   MySQL indexes will be able to parse through an int list fastest  Anything Security Use  BINARY x   or BLOB x   You can store security tokens  etc   as hex directly in BINARY x  or BLOB x    To retrieve from binary-type  use SELECT HEX field     or SELECT      WHERE field   UNHEX  quot ABCD     quot    Anything Date Use  DATETIME  DATE  or TIME  Always use DATETIME if you need to store both date and time  instead of a pair of fields   as a DATETIME indexing is more amenable to date-comparisons in MySQL  Anything True-False Use  BIT 1   MySQL 8-only    Otherwise  use BOOLEAN 1   BOOLEAN is actually just an alias of TINYINT 1   which actually stores 0 to 255  not exactly a true false  is it    Anything You Want to call  SUM      MAX     or similar functions on Use  INT 11   VARCHAR or other types of fields won t work with the SUM    etc   functions  Anything Over 1 000 Characters Use  TEXT  Max limit is 65 535  Anything Over 65 535 Characters Use  MEDIUMTEXT  Max limit is 16 777 215  Anything Over 16 777 215 Characters Use  LONGTEXT  Max limit is 4 294 967 295  FirstName  LastName Use   VARCHAR 255   UTF-8 characters can take up three characters per visible character  and some cultures do not distinguish firstname and lastname   Additionally  cultures may have disagreements about which name is first and which name is last   You should name these fields Person GivenName and Person FamilyName  Email Address Use   VARCHAR 256   The definition of an e-mail path is set in RFC821 in 1982   The maximum limit of an e-mail was set by RFC2821 in 2001  and these limits were kept unchanged by RFC5321 in 2008    See the section  4 5 3 1   Size Limits and Minimums    RFC3696  published 2004  mistakenly cites the email address limit as 320 characters  but this was an  quot info-only quot  RFC that explicitly  quot defines no standards quot  according to its intro  so disregard it  Phone Use  VARCHAR 255   You never know when the phone number will be in the form of  quot 1800    quot   or  quot 1-800 quot   or  quot 1- 800  quot   or if it will end with  quot ext  42 quot   or  quot ask for susan quot   ZipCode Use  VARCHAR 10   You ll get data like 12345 or 12345-6789   Use validation to cleanse this input  URL Use  VARCHAR 2000   Official standards support URL s much longer than this  but few modern browsers support URL s over 2 000 characters   See this SO answer  What is the maximum length of a URL in different browsers  Price Use  DECIMAL 11 2   It goes up to 11

User · Answer

I am doing about the same thing  and here s what I did   I used separate tables for name  address  email  and numbers  each with a NameID column that is a foreign key on everything except the Name table  on which it is the primary clustered key   I used MainName and FirstName instead of LastName and FirstName to allow for business entries as well as personal entries  but you may not have a need for that   The NameID column gets to be a smallint in all the tables because I m fairly certain I won t make more than 32000 entries   Almost everything else is varchar n  ranging from 20 to 200  depending on what you wanna store  Birthdays  comments  emails  really long names    That is really dependent on what kind of stuff you re storing   The Numbers table is where I deviate from that   I set it up to have five columns labeled NameID  Phone   CountryCode  Extension  and PhoneType   I already discussed NameID   Phone  is varchar 12  with a check constraint looking something like this   CHECK  Phone  like   0-9  0-9  0-9 - 0-9  0-9  0-9 - 0-9  0-9  0-9  0-9      This ensures that only what I want makes it into the database and the data stays very consistent   The extension and country codes I called nullable smallints  but those could be varchar if you wanted to   PhoneType is varchar 20  and is not nullable   Hope this helps

User · Answer

In my experience  first name last name fields should be at least 48 characters -- there are names from some countries such as Malaysia or India that are very long in their full form   Phone numbers and postcodes you should always treat as text  not numbers  The normal reason given is that there are postcodes that begin with 0  and in some countries  phone numbers can also begin with 0  But the real reason is that they aren t numbers -- they re identifiers that happen to be made up of numerical digits  and that s ignoring countries like Canada that have letters in their postcodes   So store them in a text field   In MySQL you can use VARCHAR fields for this type of information  Whilst it sounds lazy  it means you don t have to be too concerned about the right minimum size

User · Answer

Since you re going to be dealing with data of a variable length  names  email addresses   then you d be wanting to use VARCHAR  The amount of space taken up by a VARCHAR field is  field length    1 bytes  up to max length 255  so I wouldn t worry too much about trying to find a perfect size  Take a look at what you d imagine might be the longest length might be  then double it and set that as your VARCHAR limit  That said      I generally set email fields to be VARCHAR 100  - i haven t come up with a problem from that yet  Names I set to VARCHAR 50    As the others have said  phone numbers and zip postal codes are not actually numeric values  they re strings containing the digits 0-9  and sometimes more    and therefore you should treat them as a string  VARCHAR 20  should be well sufficient   Note that if you were to store phone numbers as integers  many systems will assume that a number starting with 0 is an octal  base 8  number  Therefore  the perfectly valid phone number  0731602412  would get put into your database as the decimal number  124192010

User · Answer

Someone s going to post a much better answer than this  but just wanted to make the point that personally I would never store a phone number in any kind of integer field  mainly because    You don t need to do any kind of arithmetic with it  and Sooner or later someone s going to try to  do something like  put brackets around their area code    In general though  I seem to almost exclusively use    INT 11  for anything that is either an ID or references another ID DATETIME for time stamps VARCHAR 255  for anything guaranteed to be under 255 characters  page titles  names  etc  TEXT for pretty much everything else    Of course there are exceptions  but I find that covers most eventualities

User · Answer

Someone s going to post a much better answer than this  but just wanted to make the point that personally I would never store a phone number in any kind of integer field  mainly because    You don t need to do any kind of arithmetic with it  and Sooner or later someone s going to try to  do something like  put brackets around their area code    In general though  I seem to almost exclusively use    INT 11  for anything that is either an ID or references another ID DATETIME for time stamps VARCHAR 255  for anything guaranteed to be under 255 characters  page titles  names  etc  TEXT for pretty much everything else    Of course there are exceptions  but I find that covers most eventualities

User · Answer

In my experience  first name last name fields should be at least 48 characters -- there are names from some countries such as Malaysia or India that are very long in their full form   Phone numbers and postcodes you should always treat as text  not numbers  The normal reason given is that there are postcodes that begin with 0  and in some countries  phone numbers can also begin with 0  But the real reason is that they aren t numbers -- they re identifiers that happen to be made up of numerical digits  and that s ignoring countries like Canada that have letters in their postcodes   So store them in a text field   In MySQL you can use VARCHAR fields for this type of information  Whilst it sounds lazy  it means you don t have to be too concerned about the right minimum size

User · Answer

Since you re going to be dealing with data of a variable length  names  email addresses   then you d be wanting to use VARCHAR  The amount of space taken up by a VARCHAR field is  field length    1 bytes  up to max length 255  so I wouldn t worry too much about trying to find a perfect size  Take a look at what you d imagine might be the longest length might be  then double it and set that as your VARCHAR limit  That said      I generally set email fields to be VARCHAR 100  - i haven t come up with a problem from that yet  Names I set to VARCHAR 50    As the others have said  phone numbers and zip postal codes are not actually numeric values  they re strings containing the digits 0-9  and sometimes more    and therefore you should treat them as a string  VARCHAR 20  should be well sufficient   Note that if you were to store phone numbers as integers  many systems will assume that a number starting with 0 is an octal  base 8  number  Therefore  the perfectly valid phone number  0731602412  would get put into your database as the decimal number  124192010

[mysql] Common MySQL fields and their appropriate data types

Any Table ID

Anything Security

Anything Date

Anything True-False

Anything You Want to call `SUM()`, `MAX()`, or similar functions on

Anything Over 1,000 Characters

Anything Over 65,535 Characters

Anything Over 16,777,215 Characters

FirstName, LastName

Email Address

Phone

ZipCode

URL

Price

Examples related to mysql

Examples related to database

Examples related to types