mysql中表的知识点总结

数据库 2024年05月09日 18:42

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

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'\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)

碎片大小计算

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

数据总大小=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'\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库表常用命令总结

登陆同时修改命令提示符:主机-用户-数据库-日期 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中表的知识点总结”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！

您或许对下面这些文章有兴趣: 本月吐槽辛苦排行榜

小编

TA的专栏：小编 | 小编的博客 | 我要投稿

关于本文的作者

本文标签：MySQL

所属分类：数据库

链接地址：http://www.lamuba.com/atc-471739396/

浏览前页：windows11怎么将应用添加到主屏幕

浏览后页：双线网站主机有什么优势如何鉴别真假双线网站主机

看贴要回贴有N种理由!看帖不回贴的后果你懂得的！

免费资源部落

mysql中表的知识点总结

您或许对下面这些文章有兴趣: 本月吐槽辛苦排行榜

部落快速搜索栏

各类专题梳理

网站导航栏

免费资源重点推荐

最新文章推荐

部落最新评论列表

关注我们

部落本月最受关注的热点

部落本月踩得最多的宝贝

谁在关注