Tutorial by Examples

select '123' * 2; To make the multiplication with 2 MySQL automatically converts the string 123 into a number. Return value: 246 The conversion to a number starts from left to right. If the conversion is not possible the result is 0 select '123ABC' * 2 Return value: 246 select 'A...

Suggested max len First, I will mention some common strings that are always hex, or otherwise limited to ASCII. For these, you should specify CHARACTER SET ascii (latin1 is ok) so that it will not waste space: UUID CHAR(36) CHARACTER SET ascii -- or pack into BINARY(16) country_code CHAR(2) CHAR...

Any size of INT may be used for AUTO_INCREMENT. UNSIGNED is always appropriate. Keep in mind that certain operations "burn" AUTO_INCREMENT ids. This could lead to an unexpected gap. Examples: INSERT IGNORE and REPLACE. They may preallocate an id before realizing that it won't be need...

There is already a separate entry for "FLOAT, DOUBLE, and DECIMAL" and "ENUM". A single page on datatypes is likely to be unwieldy -- I suggest "Field types" (or should it be called "Datatypes"?) be an overview, then split into these topic pages: INTs FLO...

MySQL offers a number of different numeric types. These can be broken down into GroupTypesInteger TypesINTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINTFixed Point TypesDECIMAL, NUMERICFloating Point TypesFLOAT, DOUBLEBit Value TypeBIT

Minimal unsigned value is always 0. TypeStorage(Bytes)Minimum Value(Signed)Maximum Value(Signed)Maximum Value(Unsigned)TINYINT1-27-12827-112728-1255SMALLINT2-215-32,768215-132,767216-165,535MEDIUMINT3-223-8,388,608223-18,388,607224-116,777,215INT4-231-2,147,483,648231-12,147,483,647232-14,294,967,2...

MySQL's DECIMAL and NUMERIC types store exact numeric data values. It is recommended to use these types to preserve exact precision, such as for money. Decimal These values are stored in binary format. In a column declaration, the precision and scale should be specified Precision represents the n...

FLOAT and DOUBLE represent approximate data types. TypeStoragePrecisionRangeFLOAT4 bytes23 significant bits / ~7 decimal digits10^+/-38DOUBLE8 bytes53 significant bits / ~16 decimal digits10^+/-308 REAL is a synonym for FLOAT. DOUBLE PRECISION is a synonym for DOUBLE. Although MySQL also permits...

The BIT type is useful for storing bit-field values. BIT(M) allows storage of up to M-bit values where M is in the range of 1 to 64 You can also specify values with bit value notation. b'111' -> 7 b'10000000' -> 128 Sometimes it is handy to use 'shift' to construct a single-bit valu...

CHAR(n) is a string of a fixed length of n characters. If it is CHARACTER SET utf8mb4, that means it occupies exactly 4*n bytes, regardless of what text is in it. Most use cases for CHAR(n) involve strings that contain English characters, hence should be CHARACTER SET ascii. (latin1 will do just ...

The DATE datatype comprises the date but no time component. Its format is 'YYYY-MM-DD' with a range of '1000-01-01' to '9999-12-31'. The DATETIME type includes the time with a format of 'YYYY-MM-DD HH:MM:SS'. It has a range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. The TIMESTAMP type is...