Skip to content

Instantly share code, notes, and snippets.

@joemalski

joemalski/mysql_table_opts_datatypes.txt

Last active August 14, 2020 23:43
Show Gist options
  • Save joemalski/b7597dcdf010b13e82921e4f75a7bbe8 to your computer and use it in GitHub Desktop.
Save joemalski/b7597dcdf010b13e82921e4f75a7bbe8 to your computer and use it in GitHub Desktop.
Download ZIP
MySql Table Options and Data Types
Raw
mysql_table_opts_datatypes.txt
Table Options:
--------------
ENGINE - A database engine (or storage engine) is the underlying software
component that a database management system (DBMS) uses to create,
read, update and delete (CRUD) data from a database.
InnoDB vs MyISAM
----------------
* InnoDB has row-level locking. MyISAM only has full table-level locking.
* InnoDB has what is called referential integrity which involves supporting
foreign keys (RDBMS) and relationship constraints, MyISAM does not (DMBS).
* InnoDB supports transactions, which means you can commit and roll back.
MyISAM does not.
* InnoDB is more reliable as it uses transactional logs for auto recovery.
MyISAM does not.
CHARACTER SET / CHARSET - A character set determines what languages can be
represented in the database.
example: CHARSET = utf8
COLLATE - "Collation" specifies how data is sorted and compared in a
database. Collation provides the sorting rules, case, and accent
sensitivity properties for the data in the database.
For example, when you run a query using the ORDER BY clause,
collation determines whether or not uppercase letters and
lowercase letters are treated the same.
example: COLLATE = utf8_general_ci
ci - "case-insensitive"
cs - "case-sensitive"
ai - "accent-insensitive"
as - "accent-sensitive"
Check Available CHARACTER SET in MySQL:
---------------------------------------
mysql> SHOW CHARACTER SET;
mysql> SHOW CHARACTER SET LIKE 'latin%';
Check Available COLLATE "COLLATION" in MySQL:
---------------------------------------------
mysql> SHOW COLLATION;
mysql> SHOW COLLATION WHERE Charset = 'latin1';
References:
https://database.guide/what-is-collation-in-databases/
https://dbaclass.com/article/how-to-get-the-character-set-of-a-database-in-oracle
https://dev.mysql.com/doc/refman/5.7/en/charset-mysql.html
MySQL COMMONLY USED DATA TYPES:
-------------------------------
Numeric Data Types:
-------------------
Bit-Value Type:
---------------
BIT - "1" or "0", bit-value type
- stores things as a collection of bits (you can have 1 to 64 bits)
- default is 1
- it has no display width
- to insert a binary data, you use b'10101010' or B'10101010'
- useful functions:
bin() - returns the binary equivalent
ascii() - returns the ascii value of a character
char() - binary or ascii values to the actual character value
Integer Types:
-------------
Tip: Consider the size of the integer that you need to save to the database.
How much data do I really need?
"Display Width" is specified after the data type name, example: INT(5). It is the number formating
wherein zero padding is displayed (nothing to do with the size). It is used in tandem with ZEROFILL.
When ZEROFILL is used, it automatically adds the UNSIGNED modifier. Display Width is also Optional.
Example:
CREATE TABLE test ( col_int INT(5) UNSIGNED ZEROFILL );
INSERT INTO test (col_int) VALUES (999);
SELECT * FROM test;
+---------+
| col_int |
+---------+
| 00999 | <--- observe the "00" zero padding...
+---------+
BOOL/BOOLEAN/TINYINT - integer value (8-bits if you convert it to decimal 255)
- 1 byte
- (-128 to 127, if SIGNED)
- (0 to 255, if UNSIGNED)
- has a display width
SMALLINT - integer value (16-bits if you convert it to decimal 65535)
- 2 bytes
- (-32768 to 32767, if SIGNED)
- (0 to 65535, if UNSIGNED)
- has a display width
MEDIUMINT - integer value (24-bits if you convert it to decimal 16777215)
- 3 bytes
- (-8388608 to 8388607, if SIGNED)
- (0 to 16777215, if UNSIGNED)
- has a display width
INT - integer value (32-bits if you convert it to decimal 4294967295)
- 4 bytes
- (-2147483648 to 2147483647, if SIGNED)
- (0 to 4294967295, if UNSIGNED)
- has a display width
BIGINT - integer value (64-bits if you convert it to decimal 2(64)-1)
- 8 bytes
- (-2(63) to 263-1 , if SIGNED)
- (0 to 2(64)-1, if UNSIGNED)
- has a display width
Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC, DEC:
--------------------------------------------------------
Note: ZEROFILL will still work with DECIMAL as well as the SIGNED (default) and UNSIGNED modifiers.
"Precision is the number of digits in a number. Scale is the number of digits to the right of the
decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2."
References:
https://www.mysqltutorial.org/mysql-decimal/
https://www.got-it.ai/solutions/sqlquerychat/sql-help/data-definition/mysql-float-vs-double-vs-decimal-querychat/
https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html
DECIMAL/NUMERIC/FIXED/DEC - used when it is important to preserve exact
precision, for example with monetary data.
- Automatically rounds the value.
- maximum number of digits (M "precision")
for DECIMAL Type is 1 - 65.
- maximun number of decimal places (D "scale")
for DECIMAL Type is 0 - 30.
Syntax:
-------
- DECIMAL(M, D) or NUMERIC(M, D) or FIXED(M, D) or DEC(M, D)
where: D <= M
- DECIMAL(M) is equivalent to DECIMAL(M,0)
- DECIMAL or NUMERIC or FIXED or DEC
The default value of M is 10 in this case.
Ex: (they are the same)
-----------------------
DECIMAL(5, 2)
NUMERIC(5, 2)
FIXED(5, 2)
DEC(5, 2)
M - "precision" is the maximum number of digits (the precision). It has a range of 1 to 65.
ex: DECIMAL(5, 2)
999.99
------
M = 5
D - "scale" is the number of digits to the right of the decimal point. D <= M.
ex: DECIMAL(5, 2)
999.99
--
D = 2
Tip: For Generally Accepted Accounting Principles (GAAP) Compliance is
required or you need 4 decimal places:
DECIMAL(13, 4)
Which supports a max value of: $999,999,999.9999
Otherwise, if 2 decimal places is enough:
DECIMAL(13,2)
Which supports a max value of: $99,999,999,999.99
Reference: https://rietta.com/blog/best-data-types-for-currencymoney-in/
Floating-Point Types (Approximate Value) - FLOAT, DOUBLE:
---------------------------------------------------------
Note: ZEROFILL will still work with DOUBLE as well as the SIGNED (default) and UNSIGNED modifiers.
"Precision is the number of digits in a number. Scale is the number of digits to the right of the
decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2."
References:
https://www.got-it.ai/solutions/sqlquerychat/sql-help/data-definition/mysql-float-vs-double-vs-decimal-querychat/
https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html
FLOAT - single-precision (useful for scientific applications)
- uses 4 bytes
- represent approximate numeric data values
- standard syntax "FLOAT(P)" where "P" (precision) is the maximum number that uses 4 bytes of storage
and the range is 0 - 23, "P" is also optional.
- non-standard syntax "FLOAT(M,D)" where "M" (maximum) means that values can be stored with up to "M"
digits in total and "D" is the number of digits to the right of the decimal point.
This is like the "DECIMAL" syntax.
Syntax:
FLOAT
FLOAT(P) where "P" is between 0 or 23
FLOAT(M,D) Note: The range of "M" and "D" is not specified in the manual.
DOUBLE/REAL/DOUBLE PRECISION - double-precision (useful for scientific applications)
- uses 8 bytes
- represent approximate numeric data values
- standard syntax "DOUBLE(P)" where "P" is the maximum number that uses 8 bytes of storage
and the range is 24 - 53, "P" is also optional.
- non-standard syntax "DOUBLE(M,D)" where "M" means than values can be stored with up to
M digits in total and "D" is the number of digits to the right of the decimal point.
This is like the "DECIMAL" syntax.
Syntax:
DOUBLE
DOUBLE(P) where "P" is between 0 or 23
DOUBLE(M,D) Note: The range of "M" and "D" is not specified in the manual.
Note: You can use "REAL" or "DOUBLE PRECISION" in place of "DECIMAL" they are just the same.
Comparison of DECIMAL, FLOAT, AND DOUBLE:
-----------------------------------------
CREATE TABLE test(decimal_num DECIMAL, float_num FLOAT, double_num DOUBLE);
INSERT INTO test(decimal_num, float_num, double_num) VALUES (1.1, 1.1, 1.1);
SELECT decimal_num / 0.6, float_num / 0.6, double_num / 0.6 FROM test;
+-------------------+--------------------+--------------------+
| decimal_num / 0.6 | float_num / 0.6 | double_num / 0.6 |
+-------------------+--------------------+--------------------+
| 1.6667 | 1.8333333730697632 | 1.8333333333333335 |
+-------------------+--------------------+--------------------+
How many digits in FLOAT or DOUBLE?
see: https://stackoverflow.com/questions/14637565/
Date Data Types:
----------------
References:
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html
https://dev.mysql.com/doc/refman/5.7/en/time.html
https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html
TIME - 'hh:mm:ss.ffffff' or 'hhh:mm:ss.ffffff'
WHERE: ffffff is the fractional seconds
up to 6 decimal precision.
- '-838:59:59' to '838:59:59'
'-838:59:59.000000' to '838:59:59.000000' (with fractional second precision)
- you can set DEFAULT value
Syntax:
TIME
TIME(fsp) WHERE: fps "fractional seconds precision"
You need to explicitly specify the "fsp" so that
the data will be saved to the database.
References:
https://dev.mysql.com/doc/refman/5.6/en/year.html
https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_now
https://gist.github.com/joemalski/b7597dcdf010b13e82921e4f75a7bbe8
YEAR - 1-byte type used to represent year values
- can be declared as 4 or 2 display width
- avoid using 2 display width for year
to avoid issues
- 4-digit strings '1901' to '2155'
- 4-digit numbers 1901 to 2155
- has other formats but were going to focus on 4-digit format
"KISS" - Keep it simple s****d! principle...
- 2-digit year format but don't use it!
Dates containing 2-digit year values are ambiguous because the
century is unknown.
Year values in the range 00-69 become 2000-2069.
Year values in the range 70-99 become 1970-1999.
Reference:
https://dev.mysql.com/doc/refman/5.7/en/datetime.html
https://gist.github.com/joemalski/b7597dcdf010b13e82921e4f75a7bbe8
DATE - used for values with a date part but no time part
- format, 'YYYY-MM-DD'
- '1000-01-01' to '9999-12-31'
- YYYY part accepts 2-digit year format but don't use it!
Dates containing 2-digit year values are ambiguous because the
century is unknown.
Year values in the range 00-69 become 2000-2069.
Year values in the range 70-99 become 1970-1999.
Again, apply the "KISS" principle just use 4-digit year format.
References:
https://dev.mysql.com/doc/refman/5.7/en/datetime.html
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-literals.html
https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html
https://gist.github.com/joemalski/b7597dcdf010b13e82921e4f75a7bbe8
DATETIME - combination of year, date and time
- format:'YYYY-MM-DD hh:mm:ss.ffffff'
- DATETIME(fsp) WHERE: max fsp is 6
Note: You need to explicitly specify the "fsp" so that
the data will be saved to the database.
- Dates containing 2-digit year values are ambiguous because
the century is unknown.
Year values in the range 00-69 become 2000-2069.
Year values in the range 70-99 become 1970-1999.
References:
https://dev.mysql.com/doc/refman/5.7/en/datetime.html
https://gist.github.com/joemalski/b7597dcdf010b13e82921e4f75a7bbe8
TIMESTAMP - literally used as time stamp
- it is the combination of all Date Data Types,
TIME, YEAR, DATE and DATETIME.
- '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
- automatic default: CURRENT_TIMESTAMP, together with another
column for it to work, sample time stamp: '2020-07-16 15:39:01'
- format:'YYYY-MM-DD hh:mm:ss.ffffff'
- Dates containing 2-digit year values are ambiguous because
the century is unknown.
Year values in the range 00-69 become 2000-2069.
Year values in the range 70-99 become 1970-1999.
- TIMESTAMP(fsp) WHERE: max fsp is 6
Note: You need to explicitly specify the "fsp" so that
the data will be saved to the database.
String Data Types:
------------------
CHAR/CHARACTER - It holds a fixed length string (can contain letters,
numbers, and special characters). The fixed size is
specified in parenthesis. It can store up to 255 characters.
syntax: CHAR or CHAR(<length>)
VARCHAR - "Variable Characters", It holds a variable length string (can
contain letters, numbers, and special characters). The maximum size
is specified in parenthesis.
Max size see: https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html
Note: MySQL table has a maximum row size limit of 65,535 bytes.
The storage requirements of individual columns constrain the number of
columns that fit within a given maximum row size. Storage requirements
for some data types depend on factors such as storage engine, storage format,
and character set.
syntax: VARCHAR(<length>)
Sample Max Lenghts for VARCHAR:
-------------------------------
CREATE TABLE foo ( v VARCHAR(65532) ) ENGINE=InnoDB CHARSET=latin1;
CREATE TABLE foo ( v1 VARCHAR(32766), v2 VARCHAR(32764) ) ENGINE=InnoDB CHARSET=latin1;
CREATE TABLE foo ( v VARCHAR(21844) ) ENGINE=InnoDB CHARSET=utf8;
CREATE TABLE foo (v1 VARCHAR(10922), v2 VARCHAR(10921) ) ENGINE=InnoDB CHARSET=utf8;
Tip: If you are having trouble using VARCHAR() because you can't
specifically determine actual max length, use TEXT instead.
References:
https://dev.mysql.com/doc/refman/5.7/en/blob.html
https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html
https://www.mysqltutorial.org/mysql-text/
https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8mb3.html
https://dev.mysql.com/doc/refman/5.7/en/charset-mysql.html
https://gist.github.com/joemalski/b7597dcdf010b13e82921e4f75a7bbe8
TEXT - ideal for storing large string data (character strings)
- it can't have DEFAULT value
- automatically adjust to it's text family depending on the display size
you specify in TEXT(display_size)
- you can directly use TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT without
having to specify the display size.
TEXT Family:
------------
NOTE: This is applicable if your chosen CHARSET takes 1 byte
to store a character, for example, latin1. Sadly, utf8
doesn't which is the default CHARSET since it uses
3 bytes to store a character.
TINYTEXT (255 characters, 255 bytes)
TEXT (65,535 characters, 65 KB)
MEDIUMTEXT (16,777,215 characters, 16 MB)
LONGTEXT (4,294,967,295 characters, 4 GB) using all memory!
Note: If you don't specify the ENGINE, CHARSET or COLLATE when creating
tables, MySql will use the default which is that ENGINE=InnoDB,
CHARSET=utf8 and COLLATE=utf8_general_ci.
Binary Data Types:
------------------
BINARY - stores binary strings
- has binary character set and collation
- fixed-length just like CHAR, max size 255
- syntax: BINARY or BINARY(<length>)
VARBINARY - stores binary strings
- has binary character set and collation
- variable length just like VARCHAR, max length just like VARCHAR
- syntax: VARBINARY(<length>)
Max size see: https://dev.mysql.com/doc/refman/5.7/...
Note: MySQL table has a maximum row size limit of 65,535 bytes.
The storage requirements of individual columns constrain the
number of columns that fit within a given maximum row size.
Storage requirements for some data types depend on factors
such as storage engine, storage format, and character set.
BLOB - ideal for storing image, sound, document and other files.
- blobs are binary strings with no character set sorting, so they are
treated as numeric values
- automatically adjust to it's blob family depending on the length
you specify in BLOB(<length>) but you can't directly change the
actual size! It is better to use the BLOB's family name directly.
- you can directly use TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB without
having to specify the length
BLOB Family:
------------
TINYBLOB (255 characters, 255 bytes)
BLOB (65,535 characters, 65 kb)
MEDIUMBLOB (16,777,215 characters, 16 mb)
LONGBLOB (4,294,967,295 characters, 4 gb)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment