# MySQL操作

> 来源：梦飞idc云平台 | 原文：https://www.mfisp.com/15546.html | 发布：2023-01-10 | 更新：2023-01-10 | 分类：建站教程、投稿分享

1.1.2 text与blob  
一些注意事项：

 

1）执行大量的删除或更新操作，删除数据后会产生很大的“空洞”，为提升性能，需定期使用OPTIMIZE TABLE进行碎片整理

 

2）使用合成的索引，可根据其他列的内容建立散列值，并单独存放在数据列中，仅用于精确匹配的查询，可用MD5函数生成散列值，或者SHA1或CRC32，注意尾部带有空格生成算法的不能存储在CHAR或VARCAHR列中，会受到尾部去除的影响

 

3）避免检索大量的blob和text值，减少在网络上传输大量的数据

 

4）把blob与text分离到单表，可将原表的数据列转换成定长的数据行格式，减少主表的碎片，获得性能优势

 

1.1.3 浮点数与定点数  
浮点数：float、double

 

定点数：decimal

 

注：

 

1、浮点数会存在误差问题

 

2、货币等敏感数据应使用定点数存储

 

3、避免使用浮点数进行比较

 

4、注意浮点数一些特殊值的处理

 

1.2 字符集  
1.2.1 支持的字符集  
MySQL支持多种字符集，可在同一台服务器、同一个数据库，甚至同一个表的不同字段指定不同的字符集，有较强的灵活性

 

MySQL字符集包括了字符集(CHARACTER)和校对规则(COLLATION)两个概念，字符集定义存储字符串的方式，校对规则定义比较字符串的方式，字符集和校对规则是一对多的关系

 

1.2.2 Unicode规范  
Unicode是一种编码规范，类似ASCII码，由国际组织设计，可容纳全世界所有语言文字的编码方案，Unicode有两套标准，UCS-2和USC-4，前者2个字节表示一个字符，后者4个字节表示一个字符

 

1.2.3 字符集设置  
可以在 my.cnf 中设置：  
[mysqld]  
default-character-set=utf8  
或者在启动选项中指定：  
mysqld --default-character-set=utf8  
或者在编译的时候指定：  
./configure --with-charset=utf8

 

不设置默认会使用latin1作为服务器字符集，不指定校对规则会使用默认的校对规则

 

字符集的校对规则以字符集名称开头，以_ci(不区分大小写)_cs(区分大小写)或_bin(二进制文件)结尾

 

2 数据库DDL操作  
DDL（Data Definition Language）：数据定义语言DDL用来创建数据库中的各种对象——-表、视图、索引、同义词、聚簇等。关键字主要包括CREATE、DROP、ALTER（/VIEW/INDEX/SYN/CLUSTER）等。  
DDL操作是隐性提交的,不能rollback

 

2.1 库相关  
2.1.1 建库  
mysql> CREATE DATABASE IF NOT EXISTS TEST DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;  
1  
2.1.2 删除库  
mysql> DROP DATABASE IF EXISTS TEST;  
1  
2.2 表相关  
2.2.1 表创建  
mysql> CREATE TABLE IF NOT EXISTS `tb`(  
 `runoob_id` INT UNSIGNED AUTO_INCREMENT,  
 `runoob_title` VARCHAR(100) NOT NULL,  
 `runoob_author` VARCHAR(40) NOT NULL,  
 `submission_date` DATE,  
 PRIMARY KEY ( `runoob_id` )

 

2.2.2 表修改  
修改表名  
mysql> ALTER TABLE tb RENAME tb_new;

 

增加字段  
mysql> ALTER TABLE tb ADD (additional VARCHAR(50) NOT NULL DEFAULT 'NO');

 

修改字段数据类型  
mysql> ALTER TABLE tb MODIFY additional VARCHAR(50) NOT NULL DEFAULT 'NO';

 

修改字段名称  
mysql> ALTER TABLE tb CHANGE old new VARCHAR(50) NOT NULL DEFAULT 'NO';

 

修改字段注释  
mysql> ALTER TABLE tb MODIFY COLUMN new VARCHAR(50) COMMENT '这是注释';

 

2.2.3 表删除  
mysql> DROP TABLE IF EXISTS tb;  
1  
2.3 视图相关  
2.3.1 视图创建  
mysql> create [algorithm = {undefined | merge | temptable}]   
 view 视图名 [{属性清单}]  
 as select 语句  
 [with [cascaded|local] check option];  
 -- algorithm:选择的算法  
 -- with check option:表示更新视图时要保证在该视图的权限范围之内  
 -- undfined：表示MySQL自动选择所需使用的算法  
 -- merge：表示将视图的语句与视图的定义合并，使得视图定义的某一部分取代语句的对应部分(查询的时候把视图转换为语句合并到查询语句中去)  
 -- temptable：将视图的结果存入临时表（将视图转换为子查询，当做临时表来查）  
 -- cascaded：表示更新视图时要满足所有相关视图和表的条件  
 -- local：表示更新视图时，要满足该视图本身的定义的条件即可

 

2.3.2 视图修改  
通过create or replace view修改，无则创建，有则修改

 

可通过alter修改，仅修改

 

mysql> alter view view1  
 as select name  
 from department

 

2.3.3 视图删除  
mysql> drop view [if exists] view1;  
1  
3 表碎片整理  
3.1 简介  
InnoDB表数据存储在页中，每个页存放多条记录，这些记录以树形结构组织，称为B+树

 

聚集索引的叶子节点包含行中所有字段的值，辅助索引的叶子节点包含索引列和主键列

 

在InnoDB中，删除行行为只是被标记成已删除，MySQL会通过Purge线程异步清理未用的索引键与行，但不会把释放出来的空间继续交由操作系统分配，导致页面存在很多空洞

 

删除数据会导致页page出现空白空间，大量随机的Delete操作必然在数据文件中造成不连续的空白空间，插入数据时，空白空间会被利用起来，造成了数据物理存储顺序与逻辑顺序不同，称之数据碎片

 

3.2 查看  
使用show table status like '表名';，查看表的使用状态

  

数据总大小 = data_length + index_length

 

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

 

碎片大小 = (数据总大小 — 实际表空间文件大小 )/1024/1024 = xxx MB

 

3.3 整理  
存储引擎：

 

InnoDB：

 

mysql> ALTER TABLE table_name ENGINE = Innodb;  
1  
InnoDB还会将数据缓存到InnoDB缓存中，为保证预期，需释放系统缓存

 

[root@db-test ~]# echo 3 > /proc/sys/vm/drop_caches  
1  
MyISAM：

 

mysql> OPTIMIZE TABLE table_name;  
1  
注：

 

MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可  
在OPTIMIZE TABLE运行过程中，MySQL会锁定表  
4 表统计信息  
4.1 数据表状态查看  
使用show table status like '表名'\G

 

*************************** 1. row ***************************  
 Name: tt  
 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: NULL  
 Create_time: 2022-05-05 21:37:32  
 Update_time: NULL  
 Check_time: NULL  
 Collation: utf8mb4_general_ci  
 Checksum: NULL  
 Create_options:   
 Comment:   
1 row in set (0.00 sec)4.2 数据库使用情况  
mysql> SELECT   
TABLE_SCHEMA AS database_name,  
SUM(ROUND((DATA_LENGTH+INDEX_LENGTH+DATA_FREE)/1024.0/1024, 2)) AS Total_MB  
FROM information_schema.`TABLES` AS T1  
WHERE T1.`TABLE_SCHEMA` NOT IN('performance_schema','mysql','information_schema','sys')  
GROUP BY T1.`TABLE_SCHEMA`  
ORDER BY SUM(ROUND((DATA_LENGTH+INDEX_LENGTH+DATA_FREE)/1024.0/1024, 2)) DESC  
LIMIT 10;

 

+---------------+----------+  
| database_name | Total_MB |  
+---------------+----------+  
| db | 0.07 |  
| sys | 0.02 |  
| testdb | NULL |  
+---------------+----------+  
3 rows in set, 1 warning (0.32 sec)

 

4.3 查询InnoDB存储引擎表  
mysql> SELECT   
TABLE_SCHEMA AS database_name,  
TABLE_NAME AS table_name,  
TABLE_ROWS AS table_rows,  
ENGINE AS table_engine,  
ROUND((DATA_LENGTH)/1024.0/1024, 2) AS Data_MB,  
ROUND((INDEX_LENGTH)/1024.0/1024, 2) AS Index_MB,  
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2) AS Total_MB,  
ROUND((DATA_FREE)/1024.0/1024, 2) AS Free_MB  
FROM information_schema.`TABLES` AS T1  
WHERE T1.`TABLE_SCHEMA` NOT IN('performance_schema','mysql','information_schema','sys')  
AND T1.`ENGINE` IN ('innodb'); #可调整参数

 

12  
+---------------+------------+------------+--------------+---------+----------+----------+---------+  
| database_name | table_name | table_rows | table_engine | Data_MB | Index_MB | Total_MB | Free_MB |  
+---------------+------------+------------+--------------+---------+----------+----------+---------+  
| db | tt | 2 | InnoDB | 0.02 | 0.00 | 0.02 | 0.00 |  
| db | ty | 3 | InnoDB | 0.03 | 0.00 | 0.03 | 0.00 |  
| db | yy | 0 | InnoDB | 0.02 | 0.00 | 0.02 | 0.00 |  
+---------------+------------+------------+--------------+---------+----------+----------+---------+  
3 rows in set, 1 warning (0.01 sec)

 

4.4 查看较大的表  
#查看数据表较大的表  
mysql> SELECT   
TABLE_SCHEMA AS database_name,  
TABLE_NAME AS table_name,  
TABLE_ROWS AS table_rows,  
ENGINE AS table_engine,  
ROUND((DATA_LENGTH)/1024.0/1024, 2) AS Data_MB,  
ROUND((INDEX_LENGTH)/1024.0/1024, 2) AS Index_MB,  
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2) AS Total_MB,  
ROUND((DATA_FREE)/1024.0/1024, 2) AS Free_MB  
FROM information_schema.`TABLES` AS T1  
WHERE T1.`TABLE_SCHEMA` NOT IN('performance_schema','mysql','information_schema','sys')  
ORDER BY T1.`TABLE_ROWS` DESC  
LIMIT 10;

 

#查看数据表空间较大的表  
mysql> SELECT   
TABLE_SCHEMA AS database_name,  
TABLE_NAME AS table_name,  
TABLE_ROWS AS table_rows,  
ENGINE AS table_engine,  
ROUND((DATA_LENGTH)/1024.0/1024, 2) AS Data_MB,  
ROUND((INDEX_LENGTH)/1024.0/1024, 2) AS Index_MB,  
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2) AS Total_MB,  
ROUND((DATA_FREE)/1024.0/1024, 2) AS Free_MB  
FROM information_schema.`TABLES` AS T1  
WHERE T1.`TABLE_SCHEMA` NOT IN('performance_schema','mysql','information_schema','sys')  
ORDER BY  
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)  
DESC LIMIT 10;

 

+---------------+-------------+------------+--------------+---------+----------+----------+---------+  
| database_name | table_name | table_rows | table_engine | Data_MB | Index_MB | Total_MB | Free_MB |  
+---------------+-------------+------------+--------------+---------+----------+----------+---------+  
| db | ty | 3 | InnoDB | 0.03 | 0.00 | 0.03 | 0.00 |  
| db | tt | 2 | InnoDB | 0.02 | 0.00 | 0.02 | 0.00 |  
| db | yy | 0 | InnoDB | 0.02 | 0.00 | 0.02 | 0.00 |  
+---------------+-------------+------------+--------------+---------+----------+----------+---------+  
10 rows in set, 1 warning (0.02 sec)

 

+---------------+-------------+------------+--------------+---------+----------+----------+---------+  
| database_name | table_name | table_rows | table_engine | Data_MB | Index_MB | Total_MB | Free_MB |  
+---------------+-------------+------------+--------------+---------+----------+----------+---------+  
| db | ty | 3 | InnoDB | 0.03 | 0.00 | 0.03 | 0.00 |  
| db | tt | 2 | InnoDB | 0.02 | 0.00 | 0.02 | 0.00 |  
| db | yy | 0 | InnoDB | 0.02 | 0.00 | 0.02 | 0.00 |  
+---------------+-------------+------------+--------------+---------+----------+----------+---------+  
10 rows in set, 1 warning (0.02 sec)

 

4.5 查看碎片较多的表  
mysql> SELECT   
TABLE_SCHEMA AS database_name,  
TABLE_NAME AS table_name,  
TABLE_ROWS AS table_rows,  
ENGINE AS table_engine,  
ROUND((DATA_LENGTH)/1024.0/1024, 2) AS Data_MB,  
ROUND((INDEX_LENGTH)/1024.0/1024, 2) AS Index_MB,  
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2) AS Total_MB,  
ROUND((DATA_FREE)/1024.0/1024, 2) AS Free_MB,  
ROUND(ROUND((DATA_FREE)/1024.0/1024, 2) /ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)*100,2)AS Free_Percent  
FROM information_schema.`TABLES` AS T1  
WHERE T1.`TABLE_SCHEMA` NOT IN('performance_schema','mysql','information_schema',)  
AND ROUND(ROUND((DATA_FREE)/1024.0/1024, 2) /ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)*100,2) >10  
AND ROUND((DATA_FREE)/1024.0/1024, 2)>100  
ORDER BY ROUND(ROUND((DATA_FREE)/1024.0/1024, 2) /ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)*100,2) DESC  
LIMIT 10;

 

4.6 查看表当前自增值  
mysql> SELECT   
T2.TABLE_SCHEMA,  
T2.TABLE_NAME,   
T1.COLUMN_NAME,  
T1.COLUMN_TYPE,  
T2.AUTO_INCREMENT  
FROM information_schema.columns AS T1  
INNER JOIN information_schema.tables AS T2  
ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA  
AND T1.TABLE_NAME=T2.TABLE_NAME  
WHERE T1.EXTRA='auto_increment'  
AND T1.DATA_TYPE NOT LIKE '%bigint%'  
ORDER BY T2.AUTO_INCREMENT DESC  
LIMIT 100;

 

14  
4.7 查看无主键的表  
mysql> SELECT  
TABLE_SCHEMA AS database_name,  
TABLE_NAME AS table_name,  
TABLE_ROWS AS table_rows,  
ENGINE AS table_engine,  
ROUND((DATA_LENGTH)/1024.0/1024, 2) AS Data_MB,  
ROUND((INDEX_LENGTH)/1024.0/1024, 2) AS Index_MB,  
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2) AS Total_MB,  
ROUND((DATA_FREE)/1024.0/1024, 2) AS Free_MB  
FROM information_schema.tables  
WHERE (table_schema, table_name) NOT IN (  
SELECT DISTINCT table_schema, table_name  
FROM information_schema.columns  
WHERE COLUMN_KEY = 'PRI'  
)  
AND table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema');

---
本页由 梦飞idc云平台（https://www.mfisp.com）提供。站点导航见 https://www.mfisp.com/llms.txt