(Redirected from Data types)
,

MySQL:Data types

Contents

Integers

  • In terms of performance INT(1) and INT(11) are identical and the storage engine size is the same. Not a good idea to use more than you need [Data types and the use of memory]
  • [UNSIGNED]: if TINYINT ranges from -128 to 127, unsigned it ranges from 0 to 255. The performance is identical

Decimals

  • If BIGINT is to small, decimal can be used instead (65 digits)
  • Good for exact results for fractional numbers and financial data

Float

  • MySQL supports math with float and decimal, but float is faster

VARCHAR

  • Good to use when the maximum column length is much larger then the average length
  • When updates are rare fragmentation is not a problem
  • Like INT, VARCHAR(1) and VARCHAR(100) use the same amount of space. Not a good idea to use more than you need [Data types and the use of memory]

CHAR

  • Good for short columns
  • Good for tables with lots of updates
  • Strips trailing spaces

Binary and Varbinary

  • Store binary strings, store bytes instead of characters. Comparisons are much faster than strings

Blob and Text

  • Only use when really needed
  • It will use an implicit temp. table along with the normal table (EXPLAIN - 'Using temporary')

Enum

  • Sorts per element position in the Enum list, not per char order
  • Makes tables much smaller
  • Joins between Enum and Varchar fields is much, much slower than joins between Enums or Varchars only
  • Stores integers internally relating them with the strings of the options. Converts the ints into strings on comparisons and sorts according to the other of the options and not according to the strings itself

ID Column

  • External links must follow the same structure (Unsigned id must be linked by and Unsigned external link and so on)