MySQL 数据库使用技巧

MySQL 数据库使用技巧

1. MySQL使用了ISAM/MyISAM/InnoDB/HEAP/Berkley DB(BDB)等几种引擎(表类型)，通常缺省引擎是MyISAM。
ISAM/MyISAM强调快速读取操作,每个表是一个文件，因此表大小受限于操作系统单个文件的最大大小。
InnoDB存储引擎支持事务、行锁、外键，表和索引存放于表空间，表空间是一个或多个文件或裸空间(raw disk)。
BDB 存储引擎支持事务、页锁、外键
HEAP允许只驻留在内存里的临时表格，速度最快但不安全，用完之后必须删除，通常用来做临时表。
目前只有InnoDB和BDB引擎支持事务处理和外键
从版本3.23.43b开始，InnoDB引擎支持外键约束, 从版本4.0.2-alpha开始支持事务, 截止到版本4.1.12a仍然不支持视图。
多种引擎可以协同工作，但事务中的非事务型表(ISAM/MyISAM/HEAP)无法回滚。

2. MySQL导出数据库, 输出重定向
mysqldump -u <user> -p [--opt][-q | --quick] <db_name> [tables] > <file_path>
[-q | --quick] --不缓冲查询，直接导出至stdout
[--opt] --优化导出(同--quick --add-drop-table --add-locks --extended-insert --lock-tables)
[tables] --多个表以空格分隔 <tb1_name> <tb2_name> ...
还可以同时导出多个数据库
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

3. MySQL导入数据库备份，输入重定向
mysql -u <user> -p <db_name> < <file_path>

4. 登录命令行处理器, -p 参数表示使用密码
mysql [-h <host_name>] -u <user> -p [<db_name>]

5. 查看所有引擎、支持情况及缺省引擎
show engines;

6. 修改缺省引擎
修改MySQL配置文件my.ini(Windows平台)或/etc/my.cnf(Unix平台)相应键值
可能是以下两种格式之一
default-storage-engine=MyISAM
default-table-type=MyISAM

7. 查看所有支持的字符集
show character set;

8. 修改缺省字符集，通常缺省字符集为latin1(ISO-8859-1)
修改MySQL配置文件my.ini(Windows平台)或/etc/my.cnf(Unix平台)相应键值
default-character-set=utf8
还可以在启动服务时指定参数--language
mysqld --language=latin1 ...

9. 查看当前主机所有数据库
show databases;
mysqlshow -u <user> -p --status --OS command window

10. 查看数据库运行状态
show status;

11. 改变当前数据库，MySQL数据库不须disconnect <db_name>
use <db_name>

12. 创建表时指定表类型(数据库引擎)
注意：如果指定了不受支持的表类型，MySQL将自动改用缺省引擎(对此MySQL已经考虑进行修改)。
create table <tb_name> (...) type={BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM}

13. 修改数据表的表类型
alter table <tb_name> type = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM}

14. 从 MySQL3.23开始，在创建一个表时，可以使用关键词 TEMPORARY。
它的名字被限止在当前连接中，当连接关闭时，临时表会自动地被删除。
这就意味着，两个不同的连接可以使用同一个临时表名而不会与另一个冲突，
也不会与同名现有的表相冲突(现有表将被隐藏，直到临时表被删除)。
从 MySQL 4.0.2 开始，为了能创建临时表，必须有 CREATE TEMPORARY TABLES 权限。
create temporary table (...)

15. 缺省的，MySQL搜索是大小写不敏感的，如果需要一个列总是被当作大小写敏感的方式，必须声明它为BINARY
create table <tb_name> (c varchar(10) [not null default ''] binary, ...)

16. 查看当前数据库所有表
show tables;

17. 查看表的结构
[describe | desc] <tb_name>;

18. 查看索引
show index from <tb_name>;

19. 查看数据表数据引擎、字符集等详细信息
show table status [from <db_name>]; --指定数据库所有表
show table status [from <db_name>] like '<tb_name>' --表名支持通配符，不区分大小写
show create table <tb_name>; --仅显示建表语句、数据库引擎、字符集

20. 显示当前时间
select [now() | curdate() | curtime() | current_time];

21. IF [NOT] EXISTS的使用，仅支持[create | drop] [database | table]
create database [IF NOT EXISTS] <db_name>
drop database [IF EXISTS] <db_name>
create table [IF NOT EXISTS] <tb_name> ...
drop table [IF EXISTS] <tb_name>

22. 分析SQl语句访问过程
explain select ... ;

23. 恢复和整理磁盘空间以及数据碎片
optimize table <tb_name>; --在MySQL Command Line Client执行
myisamchk [-a | --analyze] [- | --...] <tbfile_path> --在OS Command window执行，文件路径及文件名可以使用通配符
[-a] --通过分析键值的分布改进表联结性能
[-s | -ss] --沉默模式。当错误发生时仅写输出，-ss表示非常沉默
[-r] --恢复模式。可修复几乎所有一切，除非唯一的键不是唯一
[-d] --打印出关于表的一些信息
[-w] --如果表被锁定，等待
[-e] --非常彻底地检查表。这仅在极端情况下是必要的
[-v] --冗长模式。打印更多的信息。通常与-d和-e一起使用。为了更冗长，使用-v多次(-vv, -vvv)
... --其他选项略
常用选项示例：
myisamchk *.MYI | myisamchk -a *.MYI | myisamchk -d -v *.MYI | myisamchk -s *.MYI
OPTIMIZE当前只能用于MyISAM和BDB表，MYISAMCHK只能用于MyISAM表, ISAM表使用isamchk
MYISAMCHK比OPTIMIZE速度更快更可靠
对于InnoDB类型的表整理可以通过导出表然后删除原表重新导入的方式，也可以通过将表类型改变为 MyISAM 然后再改为 InnoDB 的方式解决