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

This article introduces the relevant knowledge of "what are the MySQL data types?". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Numerical type 1.1, numerical type classification

Strict numeric types (INTEGER, SMALLINT, DECIMAL, and NUMERIC)

Approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION)

Numeric types in MySQL:

Integer type byte minimum maximum

TINYINT

one

Signed-128

Unsigned 0

Signed 127

Unsigned 255

SMALLINT

two

Signed-32768

Unsigned 0

Signed 32767

Unsigned 65535

MEDIUMINT

three

Signed-8388608

Unsigned 0

Signed 8388607

Unsigned 1677215

INT 、 INTEGER

four

Signed-2147483648

Unsigned 0

Signed 2147483647

Unsigned 4294967295

BIGINT

eight

Signed-9223372036854775808

Unsigned 0

Signed 9223372036854775807

Unsigned 18446744073709551615

Floating point type

Byte

Minimum and maximum

FLOAT

four

±1.175494351E-38

±3.402823466E+38

DOUBLE

eight

±2.2250738585072014E-308

±1.7976931348623157E+308

Fixed point type

Byte description

DEC (MMAE D)

DECIMAL (MMAE D)

Macro2

The maximum range of values is the same as that of DOUBLE, and the valid range of values for a given DECIMAL is determined by M and D.

Decide

Bit type

Byte

Minimum and maximum

BIT (M)

1-8

BIT (1)

BIT (64)

1.1.1, floating point number

If the floating point number does not write precision and scale, it will be displayed according to the actual precision value; if there is precision and scale, the rounded result will be automatically inserted, and the system will not report an error; if the fixed point number does not write precision and scale, then it will operate according to the default value decimal (1010), and if the data exceeds the precision and scale value, the system will report an error.

1.1.2, bit type

BIT (bit) type: range from 1 to 64. If not written, the default is 1 bit. For this field, you will not see the result by using the select command directly. You can read it with the bin () (displayed in binary format) or hex () (displayed in hexadecimal format) function.

Example:

Mysql > desc T2 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | id | bit (1) | YES | | | NULL | | +-+-+ 1 row in set (0 sec) mysql > insert into T2 values (1) | Query OK, 1 row affected (0.05sec) mysql > select * from T2 + | id | +-+ | | +-+ 1 row in set (0.00 sec) mysql > select bin (id), hex (id) from T2 +-+-+ | bin (id) | hex (id) | +-+-+ | 1 | 1 | +-+-+ 1 row in set (0.03 sec)

When inserting bit type data, the value is first converted to binary, and if allowed, it is inserted, and if the number of bits is less than the actual number of bits defined, the insertion fails.

Example: if you insert 2 into the table just now and the actual binary is converted to 10, which exceeds the actual number of digits defined by bit (1), it will report an exception and define id as bit (2). If you insert it, it will be successful.

Mysql > insert into T2 values (2); ERROR 1406 (22001): Data too long for column 'id' at row 1mysql > alter table T2 modify id bit (2); Query OK, 1 row affected (0.67 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > insert into T2 values (2); Query OK, 1 row affected (0.03 sec) mysql > select bin (id), hex (id) from T2 +-+-+ | bin (id) | hex (id) | +-+-+ | 1 | 1 | 10 | 2 | +-+-+ 2 rows in set (0.00 sec) 1.1.3, time and date type 1.1.3, Time date type date and time type byte minimum maximum

DATE

four

1000-01-01

9999-12-31

DATETIME

eight

1000-01-01 00:00:00

9999-12-31 23:59:59

TIMESTAMP

four

19700101080001

Sometime in 2038

TIME

three

-838PUR 59RU 59

838:59:59

YEAR

11901

2155

DATE: used to represent the year, month and day

DATETIME: used to represent the year, month, day, hour and second (support insertion format: 2007-9-312: 10 10', '2007-9-3 12: 10 10', '20070903121010', 20070903121010, etc.)

TIME: only used to represent time, minute and second

TIMESTAMP: frequently insert or update the date to the current system time

YEAR: indicates the year

Example 1: create a schedule (dt). The fields are date, time, and datetime, respectively, and insert numeric values to view the display results.

Mysql > create table dt (d date,t time,dt datetime); Query OK, 0 rows affected (0.23 sec) mysql > insert into dt values (now (), now (), now ()); Query OK, 1 row affected, 1 warning (0.05 sec) mysql > select * from dt +-+ | d | t | dt | +-+ | 2021-05-13 | 10:14:07 | 0-05-13 10:14:07 | +-+ 1 row in set (2021 sec)

Example 2: create a test table t, field id1 is of type TIMESTAMP, insert a null value, and display

Mysql > create table t (id1 timestamp); Query OK, 0 rows affected (0.22 sec) mysql > insert into t values (null); Query OK, 1 row affected (0.05 sec) mysql > select * from t +-+ | id1 | +-+ | 0-05-13 10:18:05 | +-+ 1 row in set (sec)

You can see that the system automatically creates the default value CURRENT_TIMESTAMP (system date) for id1. (note that MySQL only sets the default value for the first TIMESTAMP field in the table to the system date, and if there is a second TIMESTAMP type, the default value is set to 0)

Example 3: explain as above

Mysql > alter table t add column id2 timestQuery OK, 0 rows affected (0.48 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show create table t\ G * * 1. Row * * Table: tCreate Table: CREATE TABLE `t` (`id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP `id2` timestamp NOT NULL DEFAULT '0000-00-0000: 00 sec 00') ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00) ERROR:No query specified1.1.4, string type byte description and storage requirements

CHAR (M)

M

M is an integer between 0,255

VARCHAR (M)

M is an integer between 0,65535 and the length of the value + 1 byte

TINYBLOB

The allowed length is 0mm 255 bytes, and the length of the value is + 1 byte

BLOB

The allowed length is 065535 bytes and the length of the value is + 2 bytes

MEDIUMBLOB

The allowed length is 0" 167772150 bytes, and the length of the value is + 3 bytes

LONGBLOB

The allowed length is 4294967295 bytes, and the length of the value is + 4 bytes

TINYTEXT

The length is allowed to be 0,255 bytes and the length of the value is + 2 bytes

TEXT

The allowed length is 065535 bytes and the length of the value is + 2 bytes

MEDIUMTEXT

The allowed length is 0" 167772150 bytes, and the length of the value is + 3 bytes

LONGTEXT

The allowed length is 4294967295 bytes, and the length of the value is + 4 bytes

VARBINARY (M)

A variable length byte string with a length of 0cm bytes, with the length of the value + 1 byte

BINARY (M)

M

A fixed-length byte string with a length of 0mm bytes allowed

CHAR: fixed length, when retrieving, the trailing space is deleted

VARCHAR: variable length strings, which are preserved when retrieved

Example:

Mysql > create table varc (v varchar (4), c char (4)); Query OK, 0 rows affected (0.20 sec) mysql > insert into varc values ('abc', 'abc'); Query OK, 1 row affected (0.03 sec) mysql > select length (v), length (c) from varc +-+-+ | length (v) | length (c) | +-+-+ | 4 | 3 | +-+-+ 1 row in set (0. 01 sec) mysql > select concat (v ~), concat (c ~) from varc +-+-+ | concat (vMagneur girls') | concat (c '+') | +-+-+ | abc+ | abc+ | +-+-+ 1 row in set (0.00 sec)

BINARY: similar to char, but it is a binary string

VARBINARY: similar to varchar, it stores binary strings alone

Example:

Mysql > create table bina (c binary (3)); Query OK, 0 rows affected (0.22 sec) mysql > insert into t set cantilever accountabilityEror 1054 (42S22): Unknown column 'c'in 'field list'mysql > insert into bina set cymbals query OK, 1 row affected (0.05sec) mysql > select *, hex (c), cymbals, books, etc. +-+ | c | hex (c) | cased rooma' | caterpillar a\ 0' | caterpillar a\ 0\ 0' | +- -+ | a | 0 | 0 | 1 | +-+ 1 row in set (610000 sec)

You can find that when the BINARY value is saved, the specified field definition length is achieved by filling in "0x00" (zero bytes) at the end of the value. As you can see from the example above, for a BINARY (3) column,'a 'becomes' a\ 0\ 0' when inserted.

1.1.5, ENUM type

ENUM: 1 byte of storage is required for enumerations of 1? 255 members and 2 bytes of storage for 255? 65535 members.

Example:

Mysql > create table gend (gender enum); Query OK, 0 rows affected (0.20 sec) mysql > insert into gend values ('M'), ('F'), ('1'), (null); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0mysql > select * from gend +-+ | gender | +-+ | M | | F | | M | | NULL | +-+ 4 rows in set (0.00 sec)

From this, we can see that when it is worthwhile to insert enumerated types, the case is ignored, and the initial subscript starts with 1; when querying, it is converted to uppercase, or null (null) can be inserted.

1.1.6, SET type

SET: a string object that can contain 64 members, with different members and different storage

A collection of 1x8 members, accounting for 1 byte.

A collection of 916 members, accounting for 2 bytes.

A collection of 17 members, accounting for 3 bytes.

A collection of 25 members, accounting for 4 bytes.

A collection of 33 members, accounting for 8 bytes.

Set differs from EMUN in that Set can select more than one member at a time, while ENUM can only select one.

Example:

Mysql > create table st (col set); Query OK, 0 rows affected (0.20 sec) mysql > insert into st values ('arecaly b'), (' arecaly'), ('arecore c'), ('a'); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0mysql > insert into st values ('f'); ERROR 1265 (01000): Data truncated for column 'col' at row 1mysql > insert into st values (null) Query OK, 1 row affected (0.03 sec) mysql > select * from st;+-+ | col | +-+ | sec b | | sec d | | a | NULL | +-+ 5 rows in set (0.00 sec)

From this, we can see that set can insert multiple members or null. When inserting a definition column that does not exist, an exception insertion failure will be reported.

This is the end of the content of "what are the MySQL data types"? thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.