Data Types

When creating a table, it is important to define the correct data type for a field to ensure optimal efficiency of the database

 

Three main categories:

  • Numeric
  • Date and Time
  • String

 

 

Numeric

INT Normal Integer.  11 digits wide: 0 to 4294967295, or -2147483648 to 2147483647 signed
TINYINT Very small Integer.  4 digits wide: 0 to 255, or -128 to 127 signed
SMALLINT Small Integer.  5 digits wide: 0 to 65535, or -32768 to 32767 signed
MEDIUMINT Medium Integer.  9 digits wide: 0 to 16777215, or -8388608 to 8388607
BIGINT Large Integer.  11 digits wide: 0 to 18446744073709551615, or -9223372036854775808 to 9223372036854775807 signed
FLOAT(M,D) Floating Point number. Signed. M sets the display length, D sets the decimal places. Default to 10,2. Precision: 24 places
DOUBLE(M,D) Double Precision Floating Point number. Signed. Precision: 53 places
DECIMAL(M,D) Floating point number stored as a char

 

 

Date and Time

DATE YYYY--MM-DD Format
DATETIME YYYY-MM-DD HH:MM:SS Format
TIMESTAMP YYYYMMDDHHMMSS. Without hypens or colons. Number of seconds elapsed since Unix Epoch 1 Jan 1970
TIME HH:MM:SS Format
YEAR(M) Year in 2 or 4 (default) digit format. YEAR(2) would provide 13 for 2013

 

 

Strings

CHAR(M) Fixed Length string between 1 to 255 Characters. M defines the length, default is 1 if not specified
VARCHAR(M) Variable length string between 1 to 255 Characters. Length must be defined in M
BLOB Field with a maximum length of 65535 Characters. Binary Large OBjects. Sorts and comparisons are case sensitive
TEXT Field with a maximum length of 65535 Characters. Sorts and comparisons are case sensitive
TINYBLOB Field with a maximum length of 255 Characters. Sorts and comparisons are NOT case sensitive
TINYTEXT Field with a maximum length of 255 Characters. Sorts and comparisons are case sensitive
MEDIUMBLOB Field with a maximum length of 16777217 Characters. Sorts and comparisons are NOT case sensitive
MEDIUMTEXT Field with a maximum length of 16777217 Characters. Sorts and comparisons are case sensitive
LONGBLOB Field with a maximum length of 4294967295 Characters. Sorts and comparisons are NOT case sensitive
LONGTEXT Field with a maximum length of 4294967295 Characters. Sorts and comparisons are case sensitive
ENUM Defined list for specific values. e.g. ENUM(‘A’,’B’,’C’) only allows A, B or C (& NULL) to exist in that field

 

Leave a Reply