IceBurn Snippets

When benchmarking your database, you have several options of data types, you must concentrate in all the possible data that you may have to store in a specific field.

Choosing the right data type will make all the difference when in production environment, the right MySQL structure will have the maximum performance, which means that your applications will run faster, however bad data types for certain data will kill your application.

If you are one of those persons that use VARCHAR for everything (or almost), you’ve been missing all the MySQL power.

In this article I’m going to cover the different MySQL data types and their utility in one single page.

 

NUMERIC TYPES

BIT(M)
- M indicates the number of bits per value, from 1 to 64.
- IMPORTANT: This data type was introduced in MySQL 5.0.3, in previous versions BIT is the same as TINYINT(1)
- BIT values are returned as binary, to display them in printable form, use a conversion function like BIN(), OCT() or HEX().

TINYINT(M)
- A very small integer with a signed range from -128 to 127.
- The unsigned range is 0 to 255.
- I commonly use it to store settings fields that can be either 1 or 0, like ON or OFF. (see BOOL, BOOLEAN bellow)

SMALLINT(M)
- A small integer with a signed range from -32768 to 32767.
- The unsigned range is 0 to 65535.

MEDIUMINT(M)
- A medium integer with a signed range from -8388608 to 8388607.
- The unsigned range is 0 to 16777215.

INT(M)
- A normal size integer with a signed range from -2147483648 to 2147483647.
- The unsigned range is 0 to 4294967295.

BIGINT(M)
- A large integer with a signed range from -9223372036854775808 to 9223372036854775807.
- The unsigned range is 0 to 18446744073709551615.

FLOAT(M,D)
- A small floating point number.
- Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38.
- The represented values are the theoretical limits, the actual range might be smaller depending on your system environment.
- M is the total number of digits and D is the number of digits following the decimal point.
- Usage hints: short prices like 0,50

DOUBLE(M,D)
- A normal floating point number.
- Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308.
- The represented values are the theoretical limits, the actual range might be smaller depending on your system environment.
- M is the total number of digits and D is the number of digits following the decimal point.

FLOAT(p)
- From 0 to 24, the data type becomes FLOAT with no M or D values but from 25 to 53, the data type becomes DOUBLE with no M or D values.
- The range of the resulting column is the same as for the single-precision FLOAT or double-precision DOUBLE data types.

DECIMAL(M,D)
- M is the total number of digits and D is the number of digits after the decimal point.
- For negative numbers the decimal point and the “-” sign are not counted in M. If D is 0, values have no decimal point or fractional part.
- The maximum digits length for DECIMAL (M) is 65 (64 from 5.0.3 to 5.0.5) and 30 for decimals (D).
- The default value for M is 10 and 0 for D.

 

NUMERIC TYPES SYNONYMS

BOOL, BOOLEAN
- These types are the same as TINYINT(1).
- A value of zero is considered false and non-zero values true.

INTEGER(M)
- This type is a synonym for INT.

SERIAL
- This is equivalent to BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

DOUBLE PRECISION(M,D), REAL(M,D)
- These types are the same as DOUBLE.

DEC(M,D), NUMERIC(M,D), FIXED(M,D)
- These types are the same as DECIMAL.

 

DATE AND TIME TYPES

DATE
- The DATE type is used when you need only a date value.
- MySQL retrieves and displays DATE values in ‘YYYY-MM-DD’ format.
- The supported range is from ‘1000-01-01′ to ‘9999-12-31′.

DATETIME
- The DATETIME type is used when you need both date and time.
- MySQL displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format.
- The supported range is ‘1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59′.

TIMESTAMP
- The TIMESTAMP data type has a range of ‘1970-01-01 00:00:01′ UTC to ‘2038-01-09 03:14:07′ UTC.
- TIMESTAMP values are stored as the number of seconds since the epoch (‘1970-01-01 00:00:00′ UTC).
- A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation.
- A TIMESTAMP value is returned as a string in the format ‘YYYY-MM-DD HH:MM:SS’
- To obtain the value as a number, you should add +0 to the timestamp column.

TIME
- The range is ‘-838:59:59′ to ‘838:59:59′.
- MySQL displays TIME values in ‘HH:MM:SS’ format or ‘HHH:MM:SS’ format for large hours values.
- Illegal TIME values are converted to ‘00:00:00′.

YEAR(2|4)
- A year in two-digit or four-digit format.
- The default is four-digit format.
- In four-digit format, allowable values are from 1901 to 2155, and 0000.
- In two-digit format, allowable values are from 70 to 69 (representing years from 1970 to 2069).
- MySQL displays YEAR values in YYYY format or YY format (four and two digits respectively).

 

STRING TYPES

CHAR(length)
- The char column type has a maximum length of 255 characters.
- CHAR is a fixed-length type, so when a value has less characters than the specified maximum length, the field will be right-padded with spaces. So if a column has been defined as CHAR(20) and you want to store the word “hello”, MySQL will store “hello” followed by 15 spaces.
- Extra spaces are automatically removed from the result when the value is retrieved.

VARCHAR(length)
- This is identical to CHAR, the difference is that VARCHAR is a variable-length column type, so the values are not padded with spaces.
- MySQL automatically trims spaces from the end of strings in VARCHAR fields.
- Most of the times you should use VARCHAR over CHAR, unless you are storing strings which you know to have the same or identical length.

TINYTEXT
- TINYTEXT columns are treated as non-binary strings (character strings) and are variable column types.
- The TINYTEXT type has a maximum length of 255 characters.

TEXT
- TEXT columns are treated as non-binary strings (character strings) and are variable column types.
- The TEXT type has a maximum length of 65,535 characters.

MEDIUMTEXT
- MEDIUMTEXT columns are treated as non-binary strings (character strings) and are variable column types.
- The MEDIUMTEXT type has a maximum length of 16,777,215 characters.

LONGTEXT
- LONGTEXT columns are treated as non-binary strings (character strings) and are variable column types.
- The LONGTEXT type has a maximum length of 4,294,967,295 characters.

TINYBLOB ,
BLOB ,
MEDIUMBLOB ,
LONGBLOB
- These BLOB columns correspond to the four TEXT types and have the same maximum lengths and storage requirements
- A BLOB is a binary (byte strings) large object that can hold a variable amount of data.
- These four BLOB columns only differ in the maximum length of the values they can hold.
- Unlike TEXT columns, BLOB columns have no character set, and sorting and comparison are based on the numeric values of the bytes in column values.

ENUM
- An ENUM is a string object with a value chosen from a list of allowed values that are enumerated explicitly in the column specification at table creation time.
- Usage example: ENUM(‘value1′, ‘value2′, ‘value3′)
- ENUM allows up to 65,535 values.

SET
- A SET is a string object that can have zero or more values, each of which must be chosen from a list of allowed values specified at table creation time.
- A SET can have a maximum of 64 different members.
- Usage: SET(‘value1′, ‘value2′, ‘value3′)
- This column type defines a superset of values. This allows for zero or more values from the list you specify to be included in a field.

 

Happy MySQL Benchmarking!

Etiquetas: benchmark, data types, database, EN, MySQL