Shulou(Shulou.com)06/01 Report--

MySQL basic data types of Python full stack path series

Defining the type of data field in MySQL is very important for optimizing your database.

MySQL supports many types, which can be roughly divided into three categories:

Numeric type

Date and time type

String type

Number type type size use BIT- binary TINYINT1 byte small integer value INT or INTEGER4 byte large integer value BIGINT8 byte maximum integer value DECIMAL pair DECIMAL (M Department D), if M > D, it is DECIMAL 2 otherwise it is Dial 2 decimal value FLOAT4 byte single precision floating point value DOUBLE8 byte double precision floating point value BOOL, BOOLEAN- Boolean value

BIT [(M)]

Binary bit (101001). M represents the length of the binary bit (1-64). The default is msq1.

TINYINT [(M)] [UNSIGNED] [ZEROFILL]

A small integer, a data type used to hold a range of integer values:

Range (signed) range (unsigned)-128 to 1270 to

Special: no Boolean value in MySQL, constructed using tinyint (1).

INT [(M)] [UNSIGNED] [ZEROFILL]

An integer, a data type used to hold a range of integer values:

Range (signed) range (unsigned)-2147483648 to 21474836470 to 4294967295

The m in the integer type is for display only and there is no limit to the storage range. For example: int (5), when inserting data 2, the data is displayed as: 00002 when selected

BIGINT [(M)] [UNSIGNED] [ZEROFILL]

Large integers, data types used to hold a range of integer values:

Range (signed) range (unsigned)-9223372036854775808 to 92233720368547758070 to 18446744073709551615

DECIMAL [(M [, D])] [UNSIGNED] [ZEROFILL]

The exact decimal value, m is the total number of numbers (minus sign is not counted), d is the number after the decimal point. The maximum value of m is 65 and the maximum value of d is 30.

Special: for accurate numerical calculations, this type of decaimal is needed to store exact values because it is stored internally as a string.

FLOAT [(MMagar D)] [UNSIGNED] [ZEROFILL]

Single-precision floating point number (inexact decimal value), m is the total number of numbers, d is the number after the decimal point.

Unsigned:

-3.402823466E+38 to-1.175494351E-38

0

1.175494351E-38 to 3.402823466E+38

Signed:

0

1.175494351E-38 to 3.402823466E+38

The higher the number, the less accurate it is.

DOUBLE [(MMagar D)] [UNSIGNED] [ZEROFILL]

Double-precision floating point number (inexact decimal value), m is the total number of numbers, d is the number after the decimal point.

Unsigned:

-1.7976931348623157E+308 to-2.2250738585072014E-308

0

2.2250738585072014E-308 to 1.7976931348623157E+308

Signed:

0

2.2250738585072014E-308 to 1.7976931348623157E+308

The higher the number, the less accurate it is.

BOOL, BOOLEAN

These types are synonymous with TINYINT. Zero is considered to be false. A non-zero value is considered correct:

Mysql > SELECT IF (0, 'true',' false') +-+ | IF (0, 'true',' false') | +-+ | false | +-+ 1 row in set (0.00 sec) mysql > SELECT IF (1, 'true',' false') +-+ | IF (1, 'true',' false') | +-+ | true | +-+ 1 row in set (0.00 sec) mysql > SELECT IF (2, 'true',' false') +-+ | IF (2, 'true',' false') | +-+ | true | +-+ 1 row in set (0.00 sec)

However, the true and false values are only for 1 and 0, respectively, aliases, as follows:

Mysql > SELECT IF (0 = FALSE, 'true',' false') +-- + | IF (0 = FALSE, 'true' 'false') | +-+ | true | +-+ 1 row in set (0.01 sec) mysql > SELECT IF (1 = TRUE,' true', 'false') +-- + | IF (1 = TRUE, 'true' 'false') | +-+ | true | +-+ 1 row in set (0.00 sec) mysql > SELECT IF (2 = TRUE,' true', 'false') +-- + | IF (2 = TRUE, 'true' 'false') | +-+ | false | +-+ 1 row in set (0.00 sec) mysql > SELECT IF (2 = FALSE,' true', 'false') +-- + | IF (2 = FALSE, 'true' 'false') | +-+ | false | +-+ 1 row in set (0.00 sec) time type

The date and time types that represent time values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.

Each time type has a range of valid values and a "zero" value, which is used when specifying values that cannot be represented by illegal MySQL.

Type size (bytes) format use DATE3YYYY-MM-DD date values DATETIME8YYYY-MM-DD HHSS mixed date and time values TIMESTAMP8YYYYMMDD HHMMSS mixed date and time values Timestamp TIME3HHSS time value or duration YEAR1YYYY year value type range DATE'1000-01-01' to '9999-12-31'DATETIME'1000-01-01 0000Rod 00.000000' to' 9999-12-31 23:59:59.999999'TIMESTAMP'1970-01-01 0000Rd 001R 01.000000' UTC to '2038-01-19 03Rule 14lv 07.99999mm timekeeping color 8388V 59.000000' to' 838:59:59.000000'YEAR1901 to 2155 string type type size (bytes) use CHAR (M) 0 to 255fixed-length string Even if the data is less than M length It will also occupy M length VARCHAR (M) 0 to 65535, a variable length string, where M represents the maximum column length of the character TEXT [(M)] 0 to 65535 long text column MEDIUMTEXT0 to 16777215 medium length text column LONGTEXT4294967295 or 4GB maximum text column ENUM ('value1','value2',...)

Enumerate type SET ('value1','value2',...)

Collection type

VARCHAR (M) Note

Although VARCHAR (M) is flexible to use, the processing speed of the CHAR (M) data type is faster in terms of overall system performance, sometimes exceeding 50% of the VARCHAR (M) processing speed. Therefore, users should comprehensively consider all aspects of factors when designing the database in order to achieve the best balance.

ENUM ('value1','value2',...)

CREATE TABLE shirts (name VARCHAR (40), size ENUM ('small',' medium', 'large',' x dress shirt','large')); INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), (' polo shirt','small'); mysql > SELECT name, size FROM shirts WHERE size = 'medium'' +-+-+ | name | size | +-+-+ | t-shirt | medium | +-+-+ 1 row in set (0.00 sec) UPDATE shirts SET size = 'small' WHERE size =' large';COMMIT

SET ('value1','value2',...)

SET is a string object that can have 0 or more values, each of which must be selected from a list of allowed values that is specified when the table is created.

Mysql > CREATE TABLE myset (col SET ('adept,' baked, 'cantilevered,' d')); Query OK, 0 rows affected (0,01 sec) mysql > INSERT INTO myset (col) VALUES ('arows affected d`), (' dmema`s'), ('arecalogical'), ('arecaldd`'), ('djinajindd'), (' djinajindd'); Query OK, 5 rows affected (0,01 sec) Records: 5 Duplicates: 0 Warnings: 0mysql > SELECT col FROM myset +-+ | col | +-+ | sec d | | rows in set d | | sec d | | sec d | | Magne d | +-+ 5 sec reference:

Http://www.cnblogs.com/wupeiqi/articles/5713315.html

Http://www.runoob.com/mysql/mysql-data-types.html

Http://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html

# Python full Stack Road

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.