MySQL (III)

MySQL (III)

MySQL Data Type

MySQL supports multiple data types, including numeric, date, and string.

Value Type

MySQL mainly provides INTEGER types: TINYINT/SMALLINT/MEDIUMINT/INT (INTEGER)/BIGINT

You can add the AUTO_INCREMENT auto-increment constraint to the property field of the integer type.

1. Integer type in MySQL

Value range of Integer type

2. floating point type and fixed point type

MySQL uses floating point numbers and fixed points to represent decimals. There are two types of floating point numbers: FLOAT and DOUBLE ). The specified point type is only DECIMAL. Both the floating point type and the fixed point type can be expressed by (M, N), where M becomes the precision, indicating the total number of digits; N becomes the scale, indicating the decimal digits.

Whether it's a fixed point or a floating point number, if the precision specified by the user exceeds the precision range, it will be rounded off for processing.

Example:

Create Table test1, where the data types of fields x, y, and z are FLOAT (5, 1), DOUBLE (5, 1), and DECIMAL (5, 1). Insert 5.51, 5.12, 5.123 to the table.

create table test1(x FLOAT(5,1),y DOUBLE(5,1),z DECIMAL(5,1));

Insert data:

Insert into test1 values (5.51, 5.12, 5.123 );

View data:

Select * from test1;

3. Date and Time types

MySQL has multiple data types that indicate DATE, including DATETIME, DATE, TIMESTAMP, TIME, and YEAR.

A, YEAR

The YEAR type is a single-byte type used to represent the YEAR, in the storage is only one byte. You can specify the value of YEAR in various formats.

(1) YEAR, in the format of a 4-digit string or 4-digit number. The value range is between 1 and 2 155. The input format is 'yyyy' or YYYY. For example, enter '123' or 2010

(2) YEAR, in the format of a two-character string. The value range is '00' -- '99 '. Values in the '00' -- '69 'range are converted to YEAR values in the range of 2000-2069 and 1970-1999 respectively. Values inserted beyond the value range will be converted to 2000

(3) YEAR, in the range of 1 to 2 digits ~ 99.1 ~ 69 and 70 ~ Values in the 99 range are converted to 2001 ~ 2069 and 1970 ~ The YEAR value in the range of 1999. 0 will be converted to 0000 instead of 2000

Example:

Create a data table test2, define the field y with the Data Type of YEAR, insert 2014, '20160301', '20160301'

Insert into test2 values (2014), ('20140901'), ('20140901 ');

B. TIME

The TIME type is used for values that only require TIME information, and three bytes are required for storage. The format is 'hh: MM: ss '. HH indicates the hour; MM indicates the minute; SS indicates the second. Specify the ti me value in various formats.

(1) A string in the format of 'd HH: MM: ss.

You can also use any of the following non-strict Syntax: 'hh: MM: ss'/'hh: mm'/'d HH: MM '/'d HH'/'ss '. Here, "D" indicates a day, which can be a value between 0 and 34. When the database is inserted, D is converted to hour for storage, in the format of "D * 24 + HH"

(2) A string without separators or a value in HHMMSS format.

Example:

Create Table test3, define the Field t whose data type is TIME, and insert '11: 05: 05 ', '23: 23', '2 10', '3 02 ', '10 '.

Insert into test3 values ('11: 05: 05 '), ('23: 23'), ('2: 10 ');

C. DATE type

The DATE type is used when only the DATE is required, and there is no time, and three bytes are required for storage. The date format is 'yyyy-MM-DD'

(1) represents the date in 'yyyy-MM-DD 'or 'yyyymmdd' string format.

(2) In 'yy-MM-DD 'or 'yymmdd' string represents the date, Here YY represents two years. '00 ~ 69 'range to '2017 ~ 2069 '; '70 ~ 99 'range conversion to '100 ~ 1999 '.

(3) represents the date in YY-MM-DD or YYMMDD format. Similarly, 00-69 indicates 2000-2069; 70-99 is converted to 1970-1999.

(4) use CURRENT_DATE or NOW () to insert the current system date.

Example:

Create data table test4, define the data type DATE Field as d, insert 'yyyy-MM-DD 'and 'yyyymmdd' string format DATE

Insert into test4 values ('2017-12-11 '), (2014 );

Insert current system time:

Insert into test4 values (current_date), (now ());

D. DATETIME

The DATETIME type is used for values that need to contain both the date and time. 8 bytes are required for storage. The date format is 'yyyy-MM-DD HH: MM: ss'. When assigning values to DATETIME-type fields, you can use string-type or numeric-type data inserts.

(1) The value in 'yyyy-MM-DD HH: MM: ss' or 'yyyymmddhhmms' string format, the value range is '2017-01-01 00:00:00 '~ '2017-12-3 23:59:59'

(2) represent the date in 'yy-MM-DD HH: MM: ss' or 'yymmddhhmms' string format.

(3) represent the date and time in the digital format of YYYYMMDDHHMMSS or YYMMDDHHMMSS.

Example:

Insert into test5 values ('2017-12-11 15:27:12 '), ('20170901'), (2014 );

E, TIMESTAMP

The TIMESTAMP display format is the same as DATETIME. The display width is fixed to 19 characters and the date format is YYYY-MM-DD HH: MM: SS, which requires 4 bytes for storage. However, the value range of the TIMESTAMP column is less than the value range of DATETIME, Which is '2017-01-01 00:00:01 'UTC ~ '2017-01-19 03:14:07 'UTC. UTC is the world standard time.

Example:

Create test7, define the field ts whose data type is TIMESTAMP, and insert values '20160301', '20160301', now () to the table ().

Insert into test6 values ('20140901'), (19950101010101), (now ());

4. string type

String type in MySQL: CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET

1. CHAR and VARCHAR types

CHAR (M) is a fixed-length string and specifies the length of the string column during definition. Fill in spaces on the right side to reach the specified length. M indicates the column length in the range of 0 ~ It can contain 255 characters. For example, ch ar (4) defines a fixed-length string column with a maximum number of characters. 4. When the CHAR value is retrieved, empty spaces at the end of the column are deleted.

VARCHAR (M) is a variable-length string. M indicates the maximum column length. The range of M is 0 ~ 65535. The maximum actual length of a VARCHAR is determined by the maximum row size and the character set used. The actual occupied space is the actual length of the string plus 1. For example, VARCHAR (50) defines a string with a maximum length of 50. If the inserted string contains only 10 characters, the actually stored string is 10 strings + one ending string. When the VARCHAR value is saved and retrieved, the trailing space is retained.

2. TEXT Type

The TEXT column stores non-binary strings, such as articles and comments. When saving or querying the value of the TEXT column, trailing spaces are not deleted. There are four types of TEXT: TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT. The storage space and Data Length of different TEXT types are different.

(1) TEXT columns whose maximum length is 255 characters

(2) TEXT columns with a maximum length of 65535 characters

(3) TEXT columns with a maximum length of 16777215 characters in MEDIUMTEXT

(4) LONGTEXT columns with a maximum length of 4294967295 (4 GB) characters

3. ENUM type

ENUM is a string object whose value is a column value enumerated in the specified column when the table is created. The syntax format is as follows:

Field name ENUM ('value 1', 'value 2'... 'value n ')

Field name refers to the field to be defined, and value n refers to the nth value in the enumeration list. When a field of the ENUM type is set to a value, the value can only be set in the specified enumerated list, and only one value can be obtained at a time. If the created member contains spaces, the spaces at the end of the member are automatically deleted. The ENUM value is represented by an internal integer. Each enumerated value has an index. The List Value allows the Member value to start from 1 and MySQL stores this index number. An enumeration can contain up to 65535 elements.

For example, if a column of the ENUM type ('first', 'second', 'third') is defined, the values of this column and the indexes of each value are as follows:

 

Value	Index
NULL	NULL
"	0
First	1
Second	2
Third	3