mysql中表的知識(shí)點(diǎn)總結(jié)

這篇文章主要介紹“mysql中表的知識(shí)點(diǎn)總結(jié)”，在日常操作中，相信很多人在mysql中表的知識(shí)點(diǎn)總結(jié)問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”mysql中表的知識(shí)點(diǎn)總結(jié)”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

1.  表

創(chuàng)建表的原則：

l  禁止使用中文做字段名；

l  禁止使用字符型做主鍵；

l  禁止無(wú)主鍵或唯一索引；

1.1.  整型

int、tinyint是使用最多的整型類型。

主鍵選擇：

為什么要選擇ID做主鍵：主鍵字段要選擇不經(jīng)常修改的、盡量與業(yè)務(wù)無(wú)關(guān)、無(wú)具體含義的。因?yàn)镮nnoDB是索引組織表，需要保證索引結(jié)構(gòu)不經(jīng)常防撞，避免造成性能的消耗。

int(4) 和int(10)都是占用4字節(jié)空閑，區(qū)別就是數(shù)據(jù)庫(kù)中表示的數(shù)據(jù)位數(shù)，一個(gè)4位一個(gè)10位。有些在數(shù)字前自動(dòng)用0補(bǔ)位。

1.2.  浮點(diǎn)型

常用decimal存儲(chǔ)金錢字段，但在運(yùn)算過(guò)程中會(huì)轉(zhuǎn)成浮點(diǎn)運(yùn)算，且會(huì)出現(xiàn)四舍五入的情況，建議使用int類型。

decimal(M,D);

D是小數(shù)部分位數(shù)，超過(guò)小數(shù)部分四舍五入截?cái)啵蛔阊a(bǔ)足；

M是整數(shù)部分加小數(shù)部分的總長(zhǎng)度，即插入整數(shù)部分不能超過(guò)M-D位，否則插入失敗。

1.3.  時(shí)間類型

datetime:5.6前占8個(gè)字節(jié)，5.6后占5個(gè)字節(jié)，可用范圍比timestamp大，物理存儲(chǔ)上僅比timestamp多一個(gè)字節(jié)；

可以用int存儲(chǔ)時(shí)間，通過(guò)兩個(gè)函數(shù)轉(zhuǎn)換:unix_timestamp和from_unixtime。

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

+---------------------------------------+

| unix_timestamp('2018-11-06 16:42:00') |

+---------------------------------------+

|                            1541493720 |

+---------------------------------------+

 [mysql]>select from_unixtime(1541493720);

+---------------------------+

| from_unixtime(1541493720) |

+---------------------------+

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

+---------------------------+

從5.6開(kāi)始，datetime和timestamp都支持自動(dòng)更新為當(dāng)前時(shí)間。

1.4.  字符串類型

text和blob這種大數(shù)據(jù)類型建議不要和業(yè)務(wù)表放一起。

char和varchar區(qū)別

char：用于定長(zhǎng)字符串，范圍0~255，不夠空格補(bǔ)全存庫(kù)；超過(guò)截?cái)啵?/p>

varchar:變長(zhǎng)，范圍0~65535，不夠不補(bǔ)；超過(guò)截?cái)?；可?jié)約空間提高存儲(chǔ)效率。

varchar多用一到兩個(gè)字節(jié)記錄長(zhǎng)度，數(shù)據(jù)位占用字節(jié)小于255用1字節(jié)記錄長(zhǎng)度；超過(guò)255則用2字節(jié)；還有一位用來(lái)記錄是否為null值；

示例：varchar(100)

UTF8字符集：存儲(chǔ)空間100*3+1=301字節(jié)；

GBK字符集：存儲(chǔ)空間100*2+1=201字節(jié)；

mysql每行最大字節(jié)數(shù)65535，不同字符集下字符最大長(zhǎng)度；

使用UTF8，每個(gè)字符最多占3個(gè)字節(jié)，最大長(zhǎng)度不能超過(guò)（65535-1-2）/3=21844

使用GBK，每個(gè)字符2字節(jié)，最大長(zhǎng)度不超過(guò)(65535-1-2)/2=36766

存儲(chǔ)IP,推薦用int存儲(chǔ)，使用inet_aton和inet_ntoa兩個(gè)參數(shù)；

[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.  字符集

mysql數(shù)據(jù)庫(kù)字符集包括字符集（character）和校對(duì)規(guī)則（collation）兩個(gè)概念。

字符集：定義mysql數(shù)據(jù)庫(kù)字符串的存儲(chǔ)方式；

校對(duì)規(guī)則：定義比較字符串的方式。

常用字符集：

l  GBK：每個(gè)漢字兩個(gè)字節(jié)。

l  Latin1：停用。5.1前默認(rèn)，1漢字或字母占1字節(jié)。

l  UTF8：每個(gè)漢字3個(gè)字節(jié)。

l  UTF8mb4:是utf8的超集，每個(gè)漢字4個(gè)字節(jié)。5.7建議使用。

數(shù)據(jù)庫(kù)配置文件中相關(guān)參數(shù)

[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/ |

+--------------------------+----------------------------------+

避免漢字亂碼，做到三線統(tǒng)一；

連接端的字符集必須是UTF8;

    操作系統(tǒng)字符集必須是UTF8,

mysql數(shù)據(jù)庫(kù)字符集必須是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

--------------

數(shù)據(jù)庫(kù)臨時(shí)修改字符集：命令行執(zhí)行set names 字符集名稱;

如：set names utf8

1.6.  表碎片產(chǎn)生的原因

delete操作產(chǎn)生數(shù)據(jù)碎片，碎片占用磁盤空間且讀取效率低。

1.7.  碎片計(jì)算方法及整理過(guò)程

查看表統(tǒng)計(jì)信息：

[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)

碎片大小計(jì)算

碎片大小=數(shù)據(jù)總大小-實(shí)際表空間文件大小

數(shù)據(jù)總大小=data_length+index_length

實(shí)際表空間文件大小=rows*avg_row_length

碎片大小MB=（數(shù)據(jù)總大小-實(shí)際表空間文件大?。?1024/1024

清除碎片的兩種方法：

alter table table_name engine=innodb；會(huì)給表加寫鎖，時(shí)間長(zhǎng)。

備份原表數(shù)據(jù)，刪掉，重新導(dǎo)入新表中；

在線整理表結(jié)構(gòu)的工具pt-online-schema-charge；

Mysql 5.7后已支持在線online ddl了。

1.8.  表統(tǒng)計(jì)信息

統(tǒng)計(jì)每個(gè)庫(kù)大小

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;

統(tǒng)計(jì)庫(kù)中每個(gè)表大小

SELECT

       TABLE_NAME,

       SUM(DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE

FROM

       information_schema.`TABLES`

WHERE

       TABLE_SCHEMA = 'mysql'

GROUP BY

       TABLE_NAME;

統(tǒng)計(jì)所有數(shù)據(jù)的大小

SELECT

       SUM(DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE

FROM

       information_schema.`TABLES`;

1.9.  統(tǒng)計(jì)信息的收集方法

l  遍歷information_schema_tables，收集su表的統(tǒng)計(jì)信息；

[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:

l  重啟mysql實(shí)例

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.    MySQL庫(kù)表常用命令總結(jié)

登陸同時(shí)修改命令提示符:主機(jī)-用戶-數(shù)據(jù)庫(kù)-日期 mysql -uroot -p --prompt='\h-\u-\d-\D' 

退出：exit  quit  \q

命令行結(jié)束符號(hào)：；或\g

\c 取消當(dāng)前命令執(zhí)行，就不用刪除了；

MySQL注釋： #  或 --

\s  實(shí)例信息；

show database;顯示實(shí)例下所有數(shù)據(jù)庫(kù)；

show schemas; 顯示實(shí)例下所有數(shù)據(jù)庫(kù)；

show warings;查看警告

use database;選擇數(shù)據(jù)庫(kù)；

show full processlist;查看數(shù)據(jù)庫(kù)當(dāng)前連接情況；

select user();得到登陸用戶

select version();得到版本信息；

select now();得到當(dāng)前日期時(shí)間；

seleect database();得到當(dāng)前打開(kāi)數(shù)據(jù)庫(kù)；

create database db_name；創(chuàng)建數(shù)據(jù)據(jù)庫(kù)；

create databse if not exists test1; 檢測(cè)數(shù)據(jù)庫(kù)不存在則創(chuàng)建

create databse if not exists test1 default character set 'utf8';創(chuàng)建時(shí)指定編碼方式

show create database dbname;查看數(shù)據(jù)庫(kù)信息     

alter databse dbname default character set 'gbk';修改指定數(shù)據(jù)庫(kù)的編碼方式

drop database db_name;刪除數(shù)據(jù)庫(kù)；

show tables;查看庫(kù)下所有表；

show create table tab_name \G; 查看建表語(yǔ)句；

desc tab_name;查看表結(jié)構(gòu)；

show table status;獲取表基礎(chǔ)信息；

show index from tab_name;查看當(dāng)前表下索引情況；

create table tab_name:創(chuàng)建表；

drop table tab_name;刪除表包括結(jié)構(gòu)；

select * from tab_name;

delete from tab_name where ;或truncate table tab_name;

insert into tab_name (字段列表) values（對(duì)應(yīng)字段值）

update tab_name set :字段名=某值（where）;

到此，關(guān)于“mysql中表的知識(shí)點(diǎn)總結(jié)”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！