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

I don't know if you have any understanding of what MySQL data types are before, but today I'm here to give you a brief talk. If you are interested, let's take a look at the body part. I believe you will gain something after reading what MySQL data types are.

I. Overview:

MySQL database data can be roughly divided into the following categories, so as to achieve the database in the operation of different types of processing. The advantages of database classification are mainly as follows:

Enable the system to manipulate data according to data type

2. Prevent errors in data calculation

Classification allows you to associate behaviors that are not specific to each type. When these behaviors are performed, data classification will effectively avoid errors between different types of data, which is done by adding string numbers.

More efficient use of space.

4. Defining data types is actually defining columns in tables

II. Classification of database types

Database classification can be divided into numerical type, character type, date type, text type TEXT and compound type.

2.1 numeric

2.1.1 Integral

tinyint(integer between-127 and 128)

smallint (short integer: (-32768 thru 32767) The value takes up 2 bytes and is stored as a signed binary integer)

mediumint

auto_increment(auto increment type)

row num

int (integer defaults to 11 bytes)

bigint(large integer)

Population

Examples:

Create a table with the above types and enter 999999 values in each field, and view

mysql> insert into mydata values (999999,999999,999999,9999999,9999999,9999999);ERROR 1264 (22003): Out of range value for column 'id1' at row 1 prompt id1, error mysql> select * from mydata;+------+--------+--------+-----------+-----------+| id1 | id2 | id3 | id4 | id5 |+------+--------+--------+-----------+-----------+| 127 | -32768 | 65535 | 999999999 | 999999999 || 127 | -32768 | 655356 | 999999999 | 999999999 |+------+--------+--------+-----------+-----------+

After MySQL version 5.5 and above, each field type number exceeds the length limit, and cannot be successfully created;

mysql> insert into mydata(id1) values('NIHAO');ERROR 1366 (HY000): Incorrect integer value: 'NIHAO' for column 'id1' at row 1

Different types of data also limit the execution of insert operations

int(n) where n is the limit on the length of integer data, the maximum value of n is 255

2.2 integer modifier:

unsigned integer, modifier: Specifies that a field can hold only positive data. It can increase the range of positive integers supported for this field.

zerofill specifies zero padding (spaces by default) to pad the output value, which prevents MySQL from storing negative values.

Example: Membership card number for timeout

No 0000000000000001mysql> create table mydata2(id int(10) unsigned zerofill);Query OK, 0 rows affected (0.14 sec)mysql> insert into mydata2 values(1);Query OK, 1 row affected (0.01 sec)mysql> select * from mydata2;+------------+| id |+------------+| 0000000001 |+------------+1 row in set (0.00 sec)mysql>

2.2 floating-point numeric value

float(single precision), double(double precision), decimal(precision determination)

float and dobule are floating-point numbers, the larger the number, the less precise

Decimal is a fixed-point number, often used to store financial wages, with high precision

float(5,2): indicates that the finite length of the field is 5 digits, where the integer part is 3 digits, the decimal part is 2 digits, and the decimal point exceeds the rounding, which takes up 4 bytes.

double: 8 bytes thick

decimal: decimal used to store precision, space consuming, slow to compute. Bigint can be used instead.

8762435.543 mm=x*1000 = 8762435543select mm/1000 from tb_name;mysql> create table mydata3(tb float(4,1),td double(5,2),tc decimal(5,2));Query OK, 0 rows affected (0.09 sec)mysql> insert into mydata3 values(45.6789,45.6789,45.6789);Query OK, 1 row affected, 1 warning (0.08 sec)mysql> select * from mydata3;+------+-------+-------+| tb | td | tc |+------+-------+-------+| 45.7 | 45.68 | 45.68 |+------+-------+-------+1 row in set (0.00 sec)mysql>

III. String types

3.1 String Type Comparison

char(n)

n: Store fixed length, customize length in parentheses, and its maximum value is 255;

varchar(n)

n: Storage variable length, specify the maximum length in parentheses, the maximum value is 255, if greater than 255, you need to use TEXT type

TEXT

The TEXT type can store up to 65535 strings.

3.2 Advantages and disadvantages of three types:

Char takes up more space than varchar, but is more efficient.

char: Fixed length, insert data must be less than the specified length or automatically truncated (applicable to mysql versions less than 5.5, larger than this version will not insert data).

varchar: Length dynamic, causing data migration to increase IO pressure

String Type Modifier:

binary: case-sensitive.

Examples:

mysql> create table mychar1(ca char(10),va varchar(10));mysql> insert into mychar1 values ('1234567890111 ',' 1234567890111');ERROR 1406 (22001): Data too long for column 'ca' at row 1

Different storage engines use different usages:

MyISAM table: char type recommended

InnoDB tables: Because their internal data storage format does not distinguish between fixed-length data rows and variable-length data rows, the use of varchar has been known to reduce disk space usage and disk I/O.

TEXT Type:

The TEXT type and its subtypes are used to store longer non-binary strings:

TEXT case-insensitive

IV. Compound types

ENUM: enumeration type, only one can be selected

SET: multiple set types can be selected

Examples:

Query OK, 1 row affected (0.00 sec)mysql> create table mychar(sex enum('M','W'));Query OK, 0 rows affected (0.11 sec)mysql> insert into mychar values('M');Query OK, 1 row affected (0.01 sec)mysql> create table mychar2(type set('a','b','c','d'));Query OK, 0 rows affected (0.14 sec)mysql> insert into mychar2 values('a,b,d');mysql> select * from mychar2;+-------+| type |+-------+| a,b,d |+-------+

Description: enmu: Up to 65535 elements, starting with 1 and indexed to 0, indicating an error value or nouu,

V. Time Date Type:

5.1 date type

Format: Y-MM-DD

Support range:1000-01-01 to 9999-12-31

mysql> create table mydate(dd date);

Query OK, 0 rows affected (0.06 sec)

mysql> insert into mydate values('2017-09-10'),(20180910);

5.2 time type

mysql> create table mydate1(tt time);Query OK, 0 rows affected (0.15 sec)mysql> insert into mydate1 values('100101');Query OK, 1 row affected (0.01 sec)mysql> select * from mydate1;+----------+| tt |+----------+| 10:01:01 |+----------+1 row in set (0.00 sec)

5.3 year type

00-69: to 2000-2069 70-79 to 1970-1999

1970Unix was born as the first year of computing

Examples:

mysql> create table mydate2(yy year);Query OK, 0 rows affected (0.14 sec)mysql> insert into mydate2 values('2015'),(01),(9),(69),(2080);Query OK, 5 rows affected (0.08 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> select * from mydate2;+------+| yy |+------+| 2015 || 2001 || 2009 || 2069 || 2080 |+------+5 rows in set (0.00 sec)

5.4 datetime type and time stamp type

datetime type timestamp type

datetime type can store a wide range of values, from 1001 to 9999, with seconds precision, it encapsulates date and time into integers in the format Y-MM-DD HH:MM:SS, independent of time zone, using 8 bytes of storage space

The timestamp type holds the number of seconds since midnight GMT on January 1, 1970.

It is the same as linux timestamp, timestamp only uses 4 bytes of storage space, so its range is much smaller than datetime, only from 1970-2038, usually we still use it, improve space utilization

Examples:

mysql> create table mydate3(dt datetime);Query OK, 0 rows affected (0.21 sec)mysql> insert into mydate3 values('11111111111111') -> ;Query OK, 1 row affected (0.00 sec)mysql> select * from mydate3;+---------------------+| dt |+---------------------+| 1111-11-11 11:11:11 |+----------------------+1 row in set (0.00 sec) insert current time mysql> insert into mydate4 values(now());Query OK, 1 row affected (0.01 sec)

After reading this article, what do you think of MySQL data types? If you want to know more about it, you can continue to pay attention to our industry information section.

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.