Mysql column data type

Mysql column data type

Three data types: Numeric value, time date, and string.

Value Integer

Signed or not

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 data
Define the data display width to achieve unified display.
Type (m). m indicates the minimum width displayed.

Add the field alter table tab_int add g tinyint (3); insert into tab_int values (255,-4323,-123,-,-14432,4356546 );

Need to useLeading Zero FillingThis is called zerofill.

alter table tab_int add h int(3) zerofill;insert into tab_int values(255,-128,2423,-4323,-14432,4356546,123,2);insert into tab_int values(255,-128,2423,-4323,-14432,4356546,123,1232);insert into tab_int values(255,-128,2423,-4323,-14432,4356546,123,543);

Note:
1. The range of data is not affected.
2. If the width is large, it will not be affected or intercepted.

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

Decimal

Floating Point Number

The single precision is about 6 Characters by default.
The double precision is about 16 bits by default.

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

Allows you to control the value range.
Type (M, D)
M indicates the number of digits of All numeric values.(Excluding decimal points and symbols)
D indicates the allowed decimal places.

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;

Invalid

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

Support for scientific computing

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

Number of points (to ensure that precision is not lost)

Decimal (M, D)
Total M digits
D decimal places

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 is rounded in.

insert into num_2 values( 1234.567);

Support Filling

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

Support for unsigned

Date and Time Type

Datetime type

Year, month, day, hour, minute, second, datetime, eight bytes
The timestamp is an integer, but indicates the time and date. Four bytes
Year Month day 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 a column, + 0 can retrieve the time

select a,b+0 from datetime1;

Separators in any format are supported.

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

However, if there is ambiguity, it is not recommended to use special delimiters, which may 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 rule currently
2013-04-0 indicates the entire month of January 1, April. (Logical idea)

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

Time Type

Meaning:
1. time in a day
2. It indicates the time interval, which can be expressed in days.
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 indicates a strictly limited length.

Varchar (M)Variable Length
M indicates the allowed String Length.

Char (5) varchar (5) ''5 Characters 1 character 'abc' 5 Characters 4 characters 'abcde' 5 Characters 6 Characters

Varchar requires a byte to save the total length of the string.

M indicates the number of characters rather than the number of bytes.
However, the total length is calculated in bytes. Char can contain a maximum of 255 bytes.
For example, an error is reported as follows.

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 type restrictions, the total length of a record is also limited.
Length of A varchar: the total length is 65535.
Varchar features:
When the data type exceeds 255, two bytes are used to indicate the length.
65535-2 = 65533
The entire record requires an additional byte to save the null value of the current field. Therefore, only 65532
If it is set to not null, it can contain 65533 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 uses a uniform byte no matter how many fields have null values. Instead of one byte for each field.

Text)
There are many types of text. Tinytext and longtext indicate different string lengths.

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

Enum
It looks like a string, and an integer in the internal storage represents the field value. It can only be one. You can save up to 65536 enumerated items.

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

Set
8 bytes, which indicates a maximum of 64 States. One State is a single bit.
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 Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.