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 的方式解决