MySQL table design-column type Introduction

Author: skate
Time: 2012/02/18

 

MySQL table design ------- column type Introduction

 

Some experience in database design, rational evaluation of table increments, estimated size, number of database/table shards, and the number of whole database tables (for convenience of maintenance and performance, mySQL Databases usually contain 500 tables in the same directory.

From the basic point of view, in addition to the design principles of the Architecture table above, we should also consider the basic considerations. This is very important for large systems.
Naming rules: reasonably plan the naming rules for the entire database to facilitate development and maintenance.
Column Type Selection: This is very important for SQL optimization and data storage space. The following describes the MySQL column type.

 

Value Type:

Bit (m ):
Bit Field type. M indicates the number of digits of each value, ranging from 1 to 64. M is 1 by default.

Tinyint (m ):
The signed range is-32768 to 32767. The unsigned range is 0 to 65535. 1 byte.

Smallint (m ):
The signed range is-32768 to 32767. The unsigned range is 0 to 65535. 2 bytes.

Mediumint (m ):
The signed range is-8388608 to 8388607. The unsigned range is 0 to 16777215. 3 bytes.

INT (m ):
The signed range is-2147483648 to 2147483647. The unsigned range is 0 to 4294967295. 4 bytes.

 

Bigint (n ):
The signed range is-9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615. 8 bytes.

Decimal (m, n): m is the total number of digits, and D is the number of digits after the decimal point (scale. It is actually varchar storage.

Float (M, d ):
Small (single-precision) floating point number, M is the decimal vertical digits, and D is the digits after the decimal point. If M and D are omitted, the values are saved according to the restrictions allowed by the hardware. The single-precision floating-point number is precise to about 7 decimal places. The signed range is-3.402823466e + 38 to-1.175494351e-38. The unsigned range is 0 and 1.175494351e-38 to 3.402823466e + 38, 4

Double (M, d ):
Ordinary size (Double Precision) floating point number, M is the total number of digits after the decimal point, D is the number of digits after the decimal point. If M and D are omitted, the values are saved according to the restrictions allowed by the hardware. The double-precision floating point number is precise to about 15 decimal places. The signed range is-bytes + 308 to-2.2250738584e 4e-308, And the unsigned range is 0 and 2.2250738585072014e-308 to 1.7976931348623157e +

 

 

Numeric type processing functions:
Arithmetic Operator: Select expr;
Mathematical functions: ABS (x), the absolute value of X.
Ceil (x) returns the smallest integer not less than X.
Floor (x), returns the maximum integer not greater than X
CRC32 (x): calculates the cyclic verification code value.
Rand (), returns 0 ~ 1 direct random floating point number.
Sign (x) returns the X symbol.
Truncate (x, D), returns the number x rounded to d after the decimal point.

 

 

Date type:
Timestamp:
Retrieve and display timestamp values in 'yyyy-MM-DD hh: mm: ss' format. The supported range is '2017-01-01 00:00:00 'to July 22, 1970. 4 bytes

Datetime:
Retrieves and displays datetime values in 'yyyy-MM-DD hh: mm: ss' format. The supported range is '2017-01-01 00:00:00 'to '2017-12-31 23:59:59 '. Occupies 8 bytes.

Date:
Retrieve and display date values in 'yyyy-MM-DD 'format. The supported range is '2017-01-01 'to '2017-12-31 '. 4 bytes.

 

 

Date type processing functions
Date_sub/date_add: Adds or subtracted time.
Curdate (): returns the current date in 'yyyy-MM-DD 'or 'yyyymmdd format.
Now (): returns the current date and time value in the format of 'yyyy-MM-DD hh: mm: ss' or yyyymmddhhmmss.
Combined Use: mysql> select date_add ('2017-01-02 ', interval 31 day );
-> '2017-02-02'

 

Character Type
Char (m ):
The length is fixed to M. The length can be any value from 0 to 255. When saving the char value, fill in spaces on the right side of it to reach the specified length. When the char value is retrieved, the trailing space is deleted. Case-insensitive conversion is not performed during storage or retrieval. Space used (MB * length of each character in bytes), utf8 is 3 bytes.

Varchar (m ):
The value in the column is a variable-length string. Length can be set to a value between 0 and 65,535. The maximum valid length of a varchar is determined by the maximum row size and the character set used. The total length is 65,532 bytes. The varchar value is not filled when it is saved. When the value is saved and retrieved, the space at the end is retained, which complies with the standard SQL. Occupied Space (MB * length of each character in bytes + length record bit ).

Tinytext:
Real characters + 1 byte, 255 bytes

Text:
Real characters + 2 bytes, 65,535 bytes

Mediumtext:
Real characters + 3 bytes, 16,777,215 bytes

Longtext:
Real characters + 4 bytes, 4,294,967,295 bytes

 

Character Type handler:
Char_length (STR): the return value is the length of the string, measured in characters.
Length (STR): the return value is the length of the STR string, in bytes.
Concat (str1, str2,...): returns the string generated by the connection parameter.
Concat_ws (separator, str1, str2,...): the first parameter is the separator of other parameters. The separator is placed between the two strings to be connected.
Left (STR, Len): returns the leftmost Len character starting from Str.
Right (STR, Len); returns the rightmost Len character starting from Str.
Substring (STR, POs, Len): returns a substring of the same length as the Len character from the STR string, starting from the position POS.
Lower (STR): returns the character string STR to lowercase letters.
Upper (STR): returns the character string STR converted to uppercase letters.

 

Binary type:
Binary (m ):
MB bytes, 255 characters

Varbinary (m ):
M + 1 or m + 2 bytes; 65,533 bytes

Tinyblob:
Real bytes + 1 byte; 255 bytes

BLOB:
Real bytes + 2 bytes; 65,535 bytes

Mediumblob:
Real bytes + 3 bytes; 16,777,215 bytes

Longblob:
Real bytes + 4 bytes; 4,294,967,295 bytes

 

 

Tips for type processing:
IP Storage: inet_aton (expr), which converts the IP address to an integer.
Inet_ntoa (expr), which converts an integer to an IP address.
Mysql> select inet_aton ('2017. 207.224.40 ');
-> 3520061480
Mysql> select inet_ntoa (3520061480 );
-> '2014. 207.224.40'
IP segment: inet_aton ('192. 207.224.1 ') <= IP and IP <= inet_aton ('192. 207.224.255 ');
Varchar storage: (3*4 + 3) + 1 = 16 bits, utf8 Character Set: 15*3 + 1 = 46 bytes
Int storage: 4 bytes

 

 

 

----- End ------