# MySQL数据表操作

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

### 1 常见数据类型与字符集

 

#### 1.1 数据类型

 

##### 1.1.1 char与varchar

 

char与varchar类型类似，但保存和检索的方式不同，最大长度与是否空格被保留也不同，在存储过程中不进行大小写转换

 

##### 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;

 

##### 2.1.2 删除库

 

mysql> DROP DATABASE IF EXISTS TEST;

 

#### 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` )  
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

##### 2.2.2 表修改

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

  

### 3 表碎片整理

 

#### 3.1 简介

 

InnoDB表数据存储在页中，每个页存放多条记录，这些记录以树形结构组织，称为B+树

 

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

 

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

 

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

  

![MySQL数据表操作](https://www.mfisp.com/wp-content/uploads/2022/02/1397fb3628f64c1_1_post.jpg)

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