mysql中表的知识点总结

共计 6477 个字符，预计需要花费 17 分钟才能阅读完成。

这篇文章主要介绍“mysql 中表的知识点总结”，在日常操作中，相信很多人在 mysql 中表的知识点总结问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”mysql 中表的知识点总结”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

1. 
表

创建表的原则：

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

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

l 
禁止无主键或唯一索引；

1.1. 
整型

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

主键选择：

为什么要选择 ID 做主键：主键字段要选择不经常修改的、尽量与业务无关、无具体含义的。因为 InnoDB 是索引组织表，需要保证索引结构不经常防撞，避免造成性能的消耗。

int(4) 和 int(10) 都是占用 4 字节空闲，区别就是数据库中表示的数据位数，一个 4 位一个 10 位。有些在数字前自动用 0 补位。

1.2. 
浮点型

常用 decimal 存储金钱字段，但在运算过程中会转成浮点运算，且会出现四舍五入的情况，建议使用 int 类型。

decimal(M,D);

D 是小数部分位数，超过小数部分四舍五入截断，不足补足；

M 是整数部分加小数部分的总长度，即插入整数部分不能超过 M - D 位，否则插入失败。

1.3. 
时间类型

datetime:5.6 前占 8 个字节，5.6 后占 5 个字节，可用范围比 timestamp 大，物理存储上仅比 timestamp 多一个字节；

可以用 int 存储时间，通过两个函数转换: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 开始，datetime 和 timestamp 都支持自动更新为当前时间。

1.4. 
字符串类型

text 和 blob 这种大数据类型建议不要和业务表放一起。

char 和 varchar 区别

char：用于定长字符串，范围 0~255，不够空格补全存库；超过截断；

varchar: 变长，范围 0~65535，不够不补；超过截断；可节约空间提高存储效率。

varchar 多用一到两个字节记录长度，数据位占用字节小于 255 用 1 字节记录长度；超过 255 则用 2 字节；还有一位用来记录是否为 null 值；

示例：varchar(100)

UTF8 字符集：存储空间 100*3+1=301 字节；

GBK 字符集：存储空间 100*2+1=201 字节；

mysql 每行最大字节数 65535，不同字符集下字符最大长度；

使用 UTF8，每个字符最多占 3 个字节，最大长度不能超过（65535-1-2）/3=21844

使用 GBK，每个字符 2 字节，最大长度不超过 (65535-1-2)/2=36766

存储 IP, 推荐用 int 存储，使用 inet_aton 和 inet_ntoa 两个参数；

[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 数据库字符集包括字符集（character）和校对规则（collation）两个概念。

字符集：定义 mysql 数据库字符串的存储方式；

校对规则：定义比较字符串的方式。

常用字符集：

l 
GBK：每个汉字两个字节。

l 
Latin1：停用。5.1 前默认，1 汉字或字母占 1 字节。

l 
UTF8：每个汉字 3 个字节。

l 
UTF8mb4: 是 utf8 的超集，每个汉字 4 个字节。5.7 建议使用。

数据库配置文件中相关参数

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

+————————–+———————————-+

避免汉字乱码，做到三线统一；

连接端的字符集必须是 UTF8;

  操作系统字符集必须是 UTF8,

mysql 数据库字符集必须是 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

————–

数据库临时修改字符集：命令行执行 set names 字符集名称;

如：set names utf8

1.6. 
表碎片产生的原因

delete 操作产生数据碎片，碎片占用磁盘空间且读取效率低。

1.7. 
碎片计算方法及整理过程

查看表统计信息：

[mysql] show table status like t

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

碎片大小计算

碎片大小 = 数据总大小 - 实际表空间文件大小

数据总大小 =data_length+index_length

实际表空间文件大小 =rows*avg_row_length

碎片大小 MB=（数据总大小 - 实际表空间文件大小）/1024/1024

清除碎片的两种方法：

alter table table_name engine=innodb；会给表加写锁，时间长。

备份原表数据，删掉，重新导入新表中；

在线整理表结构的工具 pt-online-schema-charge；

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

1.8. 
表统计信息

统计每个库大小

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;

统计库中每个表大小

SELECT

  TABLE_NAME,

  SUM(DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE

FROM

  information_schema.`TABLES`

WHERE

  TABLE_SCHEMA = mysql

GROUP BY

  TABLE_NAME;

统计所有数据的大小

SELECT

  SUM(DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE

FROM

  information_schema.`TABLES`;

1.9. 
统计信息的收集方法

l 
遍历 information_schema_tables，收集 su 表的统计信息；

[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 实例

l 
show table status like‘%table_name%’;

[mysql] show table status like t

*************************** 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 库表常用命令总结

登陆同时修改命令提示符: 主机 - 用户 - 数据库 - 日期 mysql -uroot -p –prompt= \h-\u-\d-\D  

退出：exit  quit  \q

命令行结束符号：；或 \g

\c 取消当前命令执行，就不用删除了；

MySQL 注释：#  或 —

\s  实例信息；

show database; 显示实例下所有数据库；

show schemas; 显示实例下所有数据库；

show warings; 查看警告

use database; 选择数据库；

show full processlist; 查看数据库当前连接情况；

select user(); 得到登陆用户

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

select now(); 得到当前日期时间；

seleect database(); 得到当前打开数据库；

create database db_name；创建数据据库；

create databse if not exists test1; 检测数据库不存在则创建

create databse if not exists test1 default character set utf8 创建时指定编码方式

show create database dbname; 查看数据库信息  

alter databse dbname default character set gbk 修改指定数据库的编码方式

drop database db_name; 删除数据库；

show tables; 查看库下所有表；

show create table tab_name \G; 查看建表语句；

desc tab_name; 查看表结构；

show table status; 获取表基础信息；

show index from tab_name; 查看当前表下索引情况；

create table tab_name: 创建表；

drop table tab_name; 删除表包括结构；

select * from tab_name;

delete from tab_name where ; 或 truncate table tab_name;

insert into tab_name (字段列表) values（对应字段值）

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

到此，关于“mysql 中表的知识点总结”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！