MySQL column data type

Three big data types: numeric, time date, string.

value integer

Is there a sign

create table tab_int(a tinyint unsigned,b tinyint,c SMALLINT,d MEDIUMINT,e INT,f bigint);insert into tab_int values(255,-128,2423,-4323,-14432,4356546);select * from tab_int;

define the display width of the data
The display width of the data is specified to achieve a uniform display purpose.
Type (m), m indicates the minimum width to display.

增加字段alter table tab_int add g tinyint(3);insert into tab_int values(255,-128,2423,-4323,-14432,4356546,123);

You need to use a leading 0 fill to achieve the goal, called Zerofill.

 alter table tab_int add H int(3) Zerofill; Insert  into Tab_int values(255,-+,2423,-  4323,-14432,4356546,123,2); Insert  into Tab_int values(255,-+,2423,-4323 ,-14432,4356546,123,1232); Insert  into Tab_int values(255,-+,2423,- 4323,-14432,4356546,123,543);

Note:
1, does not affect the range of the number.
2, the width of the large will not be affected, will not be intercepted.

There is also a Boolean bool type, but it is an alias of tinyint (1).

decimal

floating point number

Single precision By default is about 6 bits
Double precision defaults to about 16 bits

create table num(a float,b double);insert into num values(1234567890.0123456789,1234567890.0123456789);select * from num;

support for controlling the range of values
Type (M,D)
m represents all numeric digits (excluding decimal points and symbols)
D represents the number of decimal digits allowed

create table num_1(a float(5,2),b double(8,3));insert into num_1 values(123.01,12345.012);select * from num_1;

Not legal

insert into num_1 values(1234567890.0123456789,1234567890.0123456789);insert into num_1 values(12.301,12345.012);

Support scientific calculation method

insert into num_1 values(0.23E3,456.7E2);select * from num_1;

fixed-point number (guaranteed no loss of precision)

Decimal (M,D)
M total number of digits
d Number of decimal digits

create table num_2(send_money decimal(10,2));insert into num_2 values( 1234.56);select * from num_2;

The number of extra digits will be rounded up

insert into num_2 values( 1234.567);

Support padding

alter table num_2 add money decimal(10,2) zerofill;insert into num_2 values( 1234.567,1234.3234);

Unsigned support

Date Time Type

datetime type

Month Day time seconds datetime eight bytes
The timestamp is timestamp when the integer type is stored, but the date time is expressed. Four bytes
Month Date data

create table datetime1(a datetime,b timestamp);insert into datetime1 values(‘2015-04-16 11:50:21‘,‘2015-04-16 11:50:33‘);select * from datetime1;

When retrieving columns, +0 can retrieve the time

select a,b+0 from datetime1;

Support for separators in any format

insert into datetime1 values(‘20380119031422‘,‘20380119031422‘);select a,b+0 from datetime1;

However, if there is ambiguity, it is not recommended to use special separators, which can lead to unclear logic.

insert into datetime1 values(‘20:01:19‘,‘2038-01-19 03:14:08‘);

Supports 0 values

insert into datetime1 values(0,0);

Indicates that there is no provision at present
2013-04-0 represents the entire month of April. (Logical Idea)

insert into datetime1 values(‘2013-04-0‘,‘2038-01-19 03:14:08‘);

Time Type

Meaning of the expression:
1. Time of day
2, the time interval, in the expression interval, you can use days to express
Format: D HH:MM:ss

create table t_1(age time);insert into t_1 values(‘23:12:11‘);insert into t_1 values(‘231211‘);insert into t_1 values(‘5 23:12:11‘);select * from t_1;

String Type

char (M) fixed length
M represents a strictly defined length.

varchar (M) variable length
M represents the allowable string length.

        char(5)     varchar(5)‘‘      5个字符        1个字符    ‘abc‘   5个字符        4个字符‘abcde‘5个字符        6个字符

varchar requires a byte to hold the total length of the string.

M represents the number of characters rather than the number of bytes.
But the total length is calculated in bytes. Char has a maximum of 255 bytes.
For example, the following will be an error.

create table s_1(a varchar(65535)) character set utf8;create table s_2(a varchar(65535)) character set gbk;

Note:
The maximum length of a field, in addition to the limit of the type itself, has a limit on the total length of the record.
True varchar Length: total length 65535
varchar Features:
When the type data exceeds 255, the length is represented by 2 bytes.
65535-2=65533
The entire record requires an extra byte to hold the null value of the current field. Therefore, only 65532
If there is a setting of NOT NULL, then there can be 65,533 bytes.

create table s_3(a varchar(65531),b tinyint)character set latin1;create table s_4(a varchar(65532) not null,b tinyint not null)character set latin1;

This byte can be omitted unless all fields are not NULL. A record, regardless of the number of fields that exist in null, is represented by a uniform byte. Instead of one byte per field.

Text (literal)
There are many types of text. Tinytext,longtext indicates that the string length is not the same.

create table s_5(a text,b text)character set latin1;

enum
Looks like a string, the internal storage integer represents the field value and can only be one. The most 65,536 enumerated items are saved.

create table s_6(gender enum(‘female‘,‘male‘));insert into s_6 values(‘male‘);select gender+0 from s_6;

Set
8 bytes, representing up to 64 states, one bit in a state.
1 10 100

 drop table s_7; Create table s_7 (Hobby Set(' basket ',' Football ',' Pingpang ' )); Insert  into s_7 values(' Basket,football '); Insert  into s_7 values(' Football,pingpang '); Select Hobby  from s_7; Select hobby+0  from s_7;

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL column data type