香港服务器的 MySQL数据表操作

已关闭留言

香港服务器的 MySQL数据表操作

1 常见数据类型与字符集

1.1 数据类型

1.1.1 charvarchar

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

1.1.2 textblob

一些注意事项:

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

2)使用合成的索引,可根据其他列的内容建立散列值,并单独存放在数据列中,仅用于精确匹配的查询,可用MD5函数生成散列值,或者SHA1

CRC32,注意尾部带有空格生成算法的不能存储在CHARVARCAHR列中,会受到尾部去除的影响

3)避免检索大量的blobtext值,减少在网络上传输大量的数据

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

1.1.3 浮点数与定点数

浮点数:floatdouble

定点数:decimal

注:

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

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

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

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

1.2 字符集

1.2.1 支持的字符集

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

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

1.2.2 Unicode规范

Unicode是一种编码规范,类似ASCII码,由国际组织设计,可容纳全世界所有语言文字的编码方案,Unicode有两套标准,UCS-2USC-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操作

DDLData Definition Language):数据定义语言DDL用来创建数据库中的各种对象――-表、视图、索引、同义词、聚簇等。关键字主要包括CREATEDROPALTER/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操作必然在数据文件中造成不连续的空白空间,插入数据时,空白空间会被利用起来,造成了数据物理存储顺序与逻辑顺序不同,称之数据碎片