Tables in MySQL

 

Background.  This is one of those topics where I could say a huge amount without getting anywhere too specific.  I want to keep the general discussion to a minimum based on the assumption that you have worked with tables in databases before.  In addition, I am assuming you have seen some variant of SQL before, though this is not necessary.

As such, this page will primarily give a survey of the data types available in MySQL.  It does have its idiosyncrasies. 

I will break these down into basic data types.

The following table focuses on numeric data types.  All data types are assumed to be signed unless declared to be UNSIGNED.  With all numeric data types you can choose to specify

  • M = the width of the column for display

With floating types that have places after the decimal you can choose to specify

  • D = the number of digits after the decimal point

 

Numeric Data Types
Type Signed Range Unsigned Range Storage Description
TINYINT[(M)] -128 to 127 0 to 255 1 byte used to store tiny integers
SMALLINT[(M)] -32768 to 32767 0 to 65535 2 bytes used to store small integers
MEDIUMINT[(M)] -8388608 to 8388607 0 to 1677215 3 bytes used to store medium integers
INT[(M)] -2147483648 to 2147483647 0 to 4292967295 4 bytes used to store normal integer values
BIGINT[(M)] -263 to 263 - 1 0 to 264-1 8 bytes used to store large integers
DECIMAL[(M,D)] depends on M and D   M+2 bytes used to store decimals in string format
* * * * * * * * * * * *
  Minimum Maximum    
FLOAT[(M,D)] +2.225073858507201E-38 +3.402823466E+38 4 bytes single precision floating point numbers
DOUBLE[(M,D)] +1.7976931348623157E-308 +3.402823466E+308 4 bytes single precision floating point numbers

 

I have noticed that there is nothing that is directly a boolean type in MySQL.  Fortunately, you have some more flexible options in the next set of data types that can be configured to mimic boolean values.  Though I might have used a TINYINT.

 

String Data Types
Type Maximum Size Storage Description
CHAR(M) M bytes M bytes A fixed length string column up to 255 characters
VARCHAR(M) M bytes (actual length + 1) bytes A variable length string column up to 255 characters
TINYBLOB 28 - 1 bytes (actual length + 1) bytes A binary column holding data of small size
BLOB 216 - 1 bytes (actual length + 2) bytes A binary column holding data of normal size
MEDIUMBLOB 224 - 1 bytes (actual length + 3) bytes A binary column holding data of medium size
LONGBLOB 232 - 1 bytes (actual length + 4) bytes A binary column holding data of large size
TINYTEXT 28 - 1 bytes (actual length + 1) bytes A text column holding data of small size
TEXT 216 - 1 bytes (actual length + 2) bytes A text column holding data of normal size
MEDIUMTEXT 224 - 1 bytes (actual length + 3) bytes A text column holding data of medium size
LONGTEXT 232 - 1 bytes (actual length + 4) bytes A text column holding data of large size
ENUM('value1','value2', ...) 65,565 1 or 2 bytes depending Can contain exactly one member of an enumerated set of values
SET('value1','value2', ...) 64 1 up to bytes depending Can contain a set of members of a set of values

 

Now we get into some options for dates and times.

 

Date and Time Data Types
Type Range Zero Value Storage Description
DATE 1000-01-01 to 9999-12-31 0000-00-0 3 bytes date in format yyyy-mm-dd
TIME -838:59:59 to 838:59:59 00:00:00 3 bytes time in format hh:mm:ss
DATETIME -1000-01-01 00:00:00 to
9999-12-31 23:59:59		 0000-00-00 00:00:00 8 bytes date and time in format
yyyy-mm-dd hh:mm:ss
YEAR 1901 to 2155 0000 1 byte year in format yyyy
TIMESTAMP(M) 1970 to some time in 2037   4 bytes  

 

TIMESTAMP(M)
Specification Format
TIMESTAMP(14) yyyymmddhhmmss
TIMESTAMP(12) yyyymmddhhmm
TIMESTAMP(10) yyyymmddhh
TIMESTAMP(8) yyyymmdd
TIMESTAMP(6) yyyymm
TIMESTAMP(4) yyyy
TIMESTAMP(2) yy

 

We will make use of several of these, but there are plenty we won't.