Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the summary of knowledge points in the table in mysql". In the daily operation, I believe that many people have doubts about the summary of knowledge points in the table in mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "summary of knowledge points in mysql". Next, please follow the editor to study!

1. Table

Principles for creating tables:

L prohibit the use of Chinese as field names

L prohibit the use of characters as primary keys

L prohibit no primary key or unique index

1.1. Integer type

Int and tinyint are the most frequently used integer types.

Primary key selection:

Why do you choose ID as the primary key: the primary key field should be modified infrequently, irrelevant to the business as far as possible, and with no specific meaning. Because InnoDB is an index organization table, it is necessary to ensure that the index structure is not often collision-proof to avoid performance consumption.

Int (4) and int (10) both occupy 4 bytes free, the difference is the number of data bits represented in the database, a 4-bit 10-bit. Some automatically fill bits with 0 before the number.

1.2. Floating point type

Decimal is commonly used to store the money field, but it will be converted to a floating-point operation during the operation, and it will be rounded. It is recommended to use the int type.

Decimal (MMAE D)

D is the number of decimal places, more than the decimal part rounded and truncated, not enough to make up

M is the total length of the integer part plus the decimal part, that is, the insertion of the integer part cannot exceed the MMI D bit, otherwise the insertion fails.

1.3. Time type

Datetime:5.6 occupies 8 bytes before and 5 bytes after 5.6. The available range is larger than timestamp, and there is only one more byte than timestamp in physical storage.

You can use int to store time, which is converted by two functions: unix_timestamp and from_unixtime.

[mysql] > select unix_timestamp ('2018-11-06 16 purl 42purl 00')

+-+

| | unix_timestamp ('2018-11-06 16-12-42-12) | |

+-+

| | 1541493720 |

+-+

[mysql] > select from_unixtime (1541493720)

+-+

| | from_unixtime (1541493720) | |

+-+

| | 2018-11-06 16:42:00 |

+-+

Since 5. 6, both datetime and timestamp support automatic updates to the current time.

1.4. String type

Big data types such as text and blob are not recommended to be put together with business tables.

The difference between char and varchar

Char: used for fixed-length strings with a range of 0x255, not enough spaces to complete the repository; exceeds truncation

Varchar: longer, with a range of 0mm 65535, which is not enough to make up; more than truncation; it can save space and improve storage efficiency.

Varchar uses one to two bytes to record the length. If the data bit occupies less than 255bytes, it uses 1 byte to record the length; if it exceeds 255, it uses 2 bytes; and one bit is used to record whether it is null or not.

Example: varchar (100)

UTF8 character set: storage space 100 "3" 1 "301 bytes

GBK character set: storage space 100 "2" 1 "201 bytes

The maximum number of bytes per line of mysql is 65535, and the maximum length of characters under different character sets

With UTF8, each character is limited to 3 bytes, and the maximum length cannot exceed (65535-1-2) / 3 characters 21844

Use GBK with 2 bytes per character, with a maximum length of not more than (65535-1-2) / 2x36766

Storage IP, recommended int storage, using inet_aton and inet_ntoa two parameters

[mysql] > select inet_aton ('10.98.156.210')

+-+

| | inet_aton ('10.98.156.210') |

+-+

| | 174234834 |

+-+

[mysql] > select inet_ntoa (174234834)

+-+

| | inet_ntoa (174234834) | |

+-+

| | 10.98.156.210 | |

+-+

1.5. Character set

Mysql database character set includes two concepts: character set (character) and proofreading rules (collation).

Character set: defines how mysql database strings are stored

Proofreading rules: define how strings are compared.

Common character sets:

L GBK: two bytes for each Chinese character.

L Latin1: deactivate. By default before 5.1, 1 Chinese character or letter occupies 1 byte.

L UTF8: 3 bytes for each Chinese character.

L UTF8mb4: is a superset of utf8, with 4 bytes per Chinese character. 5.7 recommended.

Related parameters in database configuration file

[mysql] > show variables like'% character%'

+-+

| | Variable_name | Value |

+-+

| | character_set_client | utf8 |

| | character_set_connection | utf8 |

| | character_set_database | utf8mb4 |

| | character_set_filesystem | binary |

| | character_set_results | utf8 |

| | character_set_server | utf8mb4 |

| | character_set_system | utf8 |

| | character_sets_dir | / usr/local/mysql/share/charsets/ |

+-+

Avoid garbled Chinese characters and achieve unity of three lines

The character set of the connection side must be UTF8

Operating system character set must be UTF8

Mysql database character set must be UTF8

[mysql] >\ s

-

Mysql Ver 14.14 Distrib 5.7.24, for linux-glibc2.12 (x86 / 64) using EditLine wrapper

Connection id: 9

Current database: mysql

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile:''

Using delimiter:

Server version: 5.7.24-log MySQL Community Server (GPL)

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8mb4

Db characterset: utf8mb4

Client characterset: utf8

Conn. Characterset: utf8

UNIX socket: / tmp/mysql.sock

Uptime: 3 hours 6 min 28 sec

Threads: 1 Questions: 299 Slow queries: 3 Opens: 372 Flush tables: 1 Open tables: 144 Queries per second avg: 0.026

-

Database temporary modification character set: command line executes set names character set name

Such as: set names utf8

1.6. The causes of table fragments

The delete operation produces data fragments, which take up disk space and are inefficient to read.

1.7. Debris calculation method and finishing process

View table statistics:

[mysql] > show table status like 't'\ G

* * 1. Row *

Name: t

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 2

Avg_row_length: 8192

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: 3

Create_time: 2018-11-06 10:20:10

Update_time: NULL

Check_time: NULL

Collation: utf8mb4_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.01 sec)

Fragment size calculation

Fragment size = Total data size-actual tablespace file size

Total data size = data_length+index_length

Actual tablespace file size = rows*avg_row_length

Fragment size MB= (Total data size-actual tablespace file size) / 1024 Universe 1024

There are two ways to remove debris:

Alter table table_name engine=innodb; will lock the table for a long time.

Back up the original table data, delete it, and re-import it into the new table

Pt-online-schema-charge, a tool for sorting out table structure online

Online online ddl has been supported since Mysql 5.7.

1.8. Table statistics

Count the size of each library

SELECT

Table_schema

Sum (data_length) / 1024 / 1024 / 1024 AS data_length

Sum (index_length) / 1024 / 1024 / 1024 AS index_length

Sum (data_length + index_length) / 1024 / 1024 / 1024 AS sum_data_index

FROM

Information_schema. TABLES

WHERE

TABLE_SCHEMA! = 'information_schema'

AND TABLE_SCHEMA! = 'mysql'

GROUP BY

Table_schema

Size of each table in the statistical library

SELECT

TABLE_NAME

SUM (DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE

FROM

Information_ schema.`TABLES`

WHERE

TABLE_SCHEMA = 'mysql'

GROUP BY

TABLE_NAME

Count the size of all the data

SELECT

SUM (DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE

FROM

Information_ schema.`TABLES`

1.9. The method of collecting statistical information

L iterate through information_schema_tables to collect statistics for the su table

[mysql] > select * from information_schema.tables where table_name='t'\ G

* * 1. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: mysql

TABLE_NAME: t

TABLE_TYPE: BASE TABLE

ENGINE: InnoDB

VERSION: 10

ROW_FORMAT: Dynamic

TABLE_ROWS: 2

AVG_ROW_LENGTH: 8192

DATA_LENGTH: 16384

MAX_DATA_LENGTH: 0

INDEX_LENGTH: 0

DATA_FREE: 0

AUTO_INCREMENT: 3

CREATE_TIME: 2018-11-06 10:20:10

UPDATE_TIME: NULL

CHECK_TIME: NULL

TABLE_COLLATION: utf8mb4_general_ci

CHECKSUM: NULL

CREATE_OPTIONS:

TABLE_COMMENT:

Restart the mysql instance

L show table status like'% table_name%'

[mysql] > show table status like 't'\ G

* * 1. Row *

Name: t

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 2

Avg_row_length: 8192

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: 3

Create_time: 2018-11-06 10:20:10

Update_time: NULL

Check_time: NULL

Collation: utf8mb4_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.01 sec)

1.10. Summary of Common commands in MySQL Library Table

Log in and modify the command prompt: host-user-database-date mysql-uroot-p-- prompt='\ h -\ u -\ d -\ D'

Exit: exit quit\ Q

Command line closing symbol:; or\ g

\ C cancel the execution of the current command and there is no need to delete it

MySQL comments: # or--

\ s instance information

Show database; displays all databases under the instance

Show schemas; displays all databases under the instance

Show warings; View warnings

Use database; Select Database

Show full processlist; to view the current database connection

Select user (); get the login user

Select version (); get version information

Select now (); get the current date and time

Seleect database (); get the currently open database

Create database db_name; creates a data base

Created if the create databse if not exists test1; detection database does not exist

Specify the encoding method when create databse if not exists test1 default character set 'utf8'; is created

Show create database dbname; view database information

Alter databse dbname default character set 'gbk'; modifies the encoding of the specified database

Drop database db_name; deletes the database

Show tables; view all tables under the library

Show create table tab_name\ G; view the table creation statement

Desc tab_name; view table structure

Show table status; gets basic information of the table

Show index from tab_name; to view the index under the current table.

Create table tab_name: creating tables

Drop table tab_name; delete table including structure

Select * from tab_name

Delete from tab_name where; or truncate table tab_name

Insert into tab_name (field list) values (corresponding field value)

Update tab_name set: field name = some value (where)

At this point, the study of "summary of the knowledge points of the table in mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.