MySQL use notes (iii)

First, numeric type

1. Numeric Type
Standard SQL contains data types INTEGER, SMALLINT, DECIMAL, NUMERIC, and float, REAL, DOUBLE. MySQL expands on this basis, adding tinyint, Mediumint, bigint three different lengths of integers, and bit types, which are used to hold bit data.

integer Type	bytes
TINYINT	1
SMALLINT	2
Mediumint	3
Int/integer	4
BIGINT	8

floating-point type	bytes
FLOAT	4
DOUBLE	8

fixed-point integer type	bytes
DEC (m,d)/decimal (m,d)	M+2

(1.1) Integer type
Integer type, MySQL supports specifying the display width in parentheses following the type name, such as INT (5), which fills the width before the number when the numeric width is less than 5 bits, and the default is int (11) If the width is not explicitly specified, which is generally used with the Zero_fill piece, as 
CREATE TABLE t1 (id int (5) zerofill), #表t1中的id列宽度为5位整数, if the number of data bits is less than 5, 0 is added before, and if the number of data bits is greater than 5, the correct data is still displayed, that is, no limit to the number of bits of data, as long as the data is guaranteed to be a valid 4-byte integer that can be represented.  
     integer types have unsigned properties, if a column is specified as Zerofill, Then MySQL will automatically add the unsigned property to the column.  
     integer types have uto_increment properties generally starting from 1, each line increases by 1, When you insert null into a auto_increment column, MySQL inserts a value that is 1 larger than the current maximum value in the column. there can be at most one auto_increment column in a table. for any column that wants to use auto_increment, it should be defined as NOT NULL and defined as primary key or defined as a unique key. For example:  
CREATE table ai (id int auto_increment NOT NULL primary key);  
CREATE table ai (id int auto_increment NOT NULL, PRIMARY key (ID));  
CREATE table ai (id int auto_increment NOT NULL, unique (ID));

(1.2) floating-point type and fixed-point number type
Fixed-point numbers are stored as strings inside MySQL, more accurate than floating-point numbers, and are suitable for representing high-precision data such as currencies. Floating-point and fixed-point numbers can be expressed in the form of "(M,d)" After the type name, and M is the total number of integers plus fractional parts, and D is the position of the decimal part. float, double the default is to display the actual precision (determined by the actual hardware and operating system) when the precision is not specified, and the defaultinteger is 10 when the decimal is not specified, and the default is 0.
For example, the default decimal to hold the data 1.23, the actual will only retain the integer digits 1, the decimal place. 23 is discarded.

(1.3) Bit type
Bit type is used to hold bit field values, bit (m) can be used to hold multi-bit binary number, M range from 1 to 64, if not write the default is 1 bits. For bit fields, direct use of the SELECT command will not see the results, and can be read in Bin () (shown in binary format) or hex () (shown in hexadecimal format) function.

2. Date and Time type

date and Time type	bytes	Minimum Value	Maximum Value
DATE	4	1000-01-01	9999-12-31
Datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	4	19700101080001	At some point in 2038.
Time	3	-838:59:59	838:59:59
Year	1	1901	2155

The timestamp value returns a string displayed as a YYYY-MM-DD HH:MM:SS format, with a display width of 19 characters, or "+0" in the timestamp column if you want to get a numeric value. If you need to insert frequently or update the date to the current system time, the timestamp is usually used, timestamp is also related to the time zone, when the date is inserted into the local time zone, and when it is removed from the database, it also needs to be displayed after the date is converted to the local time zone.

3. String type  
     mysql contains char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, set and many other string types.  
(3.1) Cahr and varchar type   The
    char length is the length declared when the table was created, and 0-255;varchar is a variable-length string. When retrieved, the Char column removes trailing spaces, and varchar retains them. For example:  
Create TABLE VC (v varchar (4), C char (4));  
insert into VC values (' AB ', ' ab '); Select Length (v), Length (c) from VC, #结果为4, 2 
Select Concat (V, ' + '), concat (c, ' + ') from VC; #显示为 AB + and ab+.  
that is, the last space in the Char column has been deleted while doing the operation, and varchar still retains the space.

(3.2) binary and varbinary types
Binary and varbinary contain binary strings and do not contain non-binary strings. When the binary value is saved, at the end of the value by padding "0x00" to reach the specified length of the field definition. For a binary (3) column, insert ' a ' and change to ' a\0\0 '.

(3.3) enum type
An enumeration of 1~255 members requires 1 bytes, and an enumeration of 255~65535 members requires 2 bytes and a maximum of 65,535 members. The member of an enumeration type is a string type : one or more characters. For example:
CREATE table T (en enum (' Hello ', ' world '));

(3.4) Set type
The set type is also a string object that can contain 0~64 members, each of which corresponds to a bit, that is, a maximum of 8 bits.
A set type can select multiple members at a time, and an enum can only select one. For example, insert multiple sets of different members in table T:
CREATE TABLE T (Col set (' A ', ' B ', ' C ', ' d '));
INSERT into t values (' A, B '), (' C,d '), (' A, C, d '); #一次插入多个记录

MySQL use notes (iii)