MySQL日常操作V15.docx
《MySQL日常操作V15.docx》由会员分享，可在线阅读，更多相关《MySQL日常操作V15.docx（56页珍藏版）》请在冰豆网上搜索。
MySQL日常操作V15
HISUN
MySQL日常操作
Version1.5
BaiYaJun
2014/1/10
根据项目实际情况，记录MySQL数据库日常开发、维护经常用到的MySQL知识点和操作方法，适用于开发及维护人员。
目录
一、常用数据类型及操作1
1、数值类型1
1）整数类型（Integer）1
2）定点类型（Fixed-Point）1
3）浮点类型（Floating-Point）2
4）Bit类型（Bit-Value）2
2、字符串型2
1）Varchar3
2）Char3
3）Text3
3、日期和时间类型4
二、表结构调整4
三、MySQL数据库导出/导入6
1、mysqldump、mysql工具6
2、导出/导入csv文件7
1）导出csv文件7
2）导入csv文件7
四、权限相关8
1、服务器启动及首次root登录：
8
2、查看当前系统中的用户信息8
3、密码设置9
4、远程访问10
5、创建数据库及用户12
五、使用XtraBackup对MySQL备份/恢复15
1、XtraBackup2.0.7安装15
2、全量备份/恢复17
3、增量备份/恢复18
六、MySQL-REPLICATION安装步骤19
1、软件下载及安装19
2、主机配置21
3、从机配置22
4、数据同步22
5、故障切换23
6、配置KeepAlived27
附录：
30
一、导入数据到RDS数据库失败的问题30
二、配置MysqlProxy连接阿里云的内网rds数据库30
三、MySQL增加Innodb数据文件过程32
四、MySQL参数thread_concurrency的使用误区33
五、使用MySQL客户端程序远程导入、导出csv文件33
六、MySQL按分隔符拆分字符串为多行37
七、MySQL存储过程输入参数为中文时入库成乱码的问题38
八、MySQL并发条件下更新数据获取唯一值的方法39
九、MySQL中随机生成固定长度字符串的方法41
十、MySQL查询自动增加序号42
修订记录
版本号
修订人
修订日期
修订内容
1.0
20130503
●初稿
1.1
20130503
●
1.5
20140110
●增加了MySQL的REPLICATION以及备份恢复等内容，增补附录内容，其它各处有少量修改和完善
一、常用数据类型及操作
1、数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括精确数值数据类型（INTEGER，SMALLINT，DECIMAL,和NUMERIC），以及近似数值数据类型（FLOAT，REAL和DOUBLEPRECISION）。
关键字INT是INTEGER的同义词，关键字DEC和FIXED是DECIMAL的同义词。
MySQL将DOUBLE视为DOUBLEPRECISION（anonstandardextension）的同义词。
在未开启REAL_AS_FLOAT模式的情况下，MySQL还将REAL视为DOUBLEPRECISION（anonstandardvariation）的同义词。
BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和NDBCLUSTER表。
1）整数类型（Integer）
整数类型包括INTEGER,INT,SMALLINT,TINYINT,MEDIUMINT,BIGINT。
MySQL支持SQL标准的整数类型INTEGER或INT和SMALLINT。
作为SQL标准的扩展，MySQL也支持整数类型TINYINT，MEDIUMINT和BIGINT。
下面的表显示了每个整数类型需要的存储和范围：
MySQL整型数据基本包括以上几种，在设计字段类型时，可根据字段的长度，适当选择以上合适的数据类型：
对于一般自增长的主键（数据量不是非常大），使用INT就可以，对于详单类的数据主键（数据量可能非常大的），建议使用BIGINT，对于只有少量可选数据的字段，如性别、状态等，可使用TINYINT
（1）或TINYINT
（2）,一般都能满足需求了。
这里要注意的就是数据类型的长度，不要把超过类型长度的数字赋给这个类型的字段
2）定点类型（Fixed-Point）
该类型包括DECIMAL,NUMERIC。
在MySQL中，NUMERIC是DECIMAL的同义词，用于存储必须为确切精度的数据值，例如货币数据。
MySQL5.5使用二进制格式保存DECIMAL。
当声明该类型的列时，可以（并且通常要）指定精度（precision）和标度（scale）；例如：
salaryDECIMAL（5,2）
在该例子中，5是精度，2是标度。
精度表示保存值的主要位数，标度表示小数点后面可以保存的位数，该类型可以表示的数据范围为-999.99至999.99
在标准SQL中，DECIMAL（M）等价于DECIMAL（M,0）。
类似地，DECIMAL等价于DECIMAL（M,0），这里M的值由具体的实现来决定，MySQL同时支持这两种格式的语法，并且默认的M的值为10。
DECIMAL类型所能表示的数据的最大位数为65，但是一个给定DECIMAL列的实际范围由指定的列的精度和小数位数来共同约束。
DECIMAL或NUMERIC的最大位数是65，但具体的DECIMAL或NUMERIC列的实际范围受具体列的精度或标度约束。
如果分配给此类列的值的小数点后位数超过指定的标度允许的范围，值将按该标度进行转换。
（具体操作与操作系统有关，一般结果均被截取到允许的位数）。
3）浮点类型（Floating-Point）
FLOAT和DOUBLE类型表示近似值的数据类型。
在MySQL中，使用四个字节来表示单精度值，而使用八个字节来表示双精度值。
这两种类型都是非精确的类型，经过一些操作后并不能保证运算的正确性，例如M*G/G不一定等于M，虽然数据库内部算法已经使其尽可能的正确，但是结果还会有偏差。
总之，对于精确度要求比较高时，建议使用DECIMAL来存储，DECIMAL在MySQL内存是以字符串存储的！
4）Bit类型（Bit-Value）
我们用的不多，不做详细阐述，这里把官方的类型说明搬过来参考：
2、字符串型
目前我们用得最多的是varchar和char，偶尔会用到text所以这里只描述这三种
1）Varchar
（1）保存可变长字符串。
比固定长度占用更少的存储空间，因为它只占用自己需要的空间。
例外情况：
使用ROW_FORMAT=FIXED创建的MyISAM表，它为每行使用固定长度的空间，可能会造成浪费。
（2）存储长度信息。
如果定义的列小于或等于255，则使用1个字节存储长度值，假设使用latin1字符集，如varchar（10）将占用11个字节的存储空间。
反过来，varchar（1000），则占用1002个字节的存储空间。
（3）节约空间，对性能有帮助。
（4）5.0版本以上，无论是取值还是保存，MySQL都会保留字符串末尾的空格。
只分配真正需要的空间
使用varchar（5）和varchar（200）保存'hello'占用空间是一样的。
——这里应该指的是磁盘上的空间。
那么使用较短列有何优势？
——巨大的优势
较大的列会使用更多的内存，因为MySQL通常会分配固定大小的内存块（如varchar（200）会用200个字符大小的内存空间）来保存值（然后对值进行trim操作，最后放入磁盘）或取值。
——这对排序或使用基于内存的临时表尤其不好。
2）Char
（1）固定长度。
（2）保存值时，去掉末尾的空格。
char常用于很短字符串或长度近似相同的字符串的时候很有用。
如存储用户密码的MD5哈希值，它的长度总是一样的。
char优于varchar的地方？
1>>对于经常改变的值，char优于varchar，因为固定长度行不容易产生碎片。
——当最长长度远大于平均长度，并且很少发生更新的时候，通常适合使用varchar。
2>>对于很短的列，char的效率也是高于varchar的。
如对于单字节字符集（如latin1），char
（1）只会占用1个字节，而varchar
（2）会占用2个字节（有一个字节用来存储长度的信息）。
3）Text
用于保存大量数据。
（1）InnoDB在它们较大的时候会使用“外部”存储区域来进行保存。
——所以需要足够的外部存储空间来保存实际的值。
（2）排序方式不同于其他字符类型，不会按照完整长度进行排序，而只是按照max_sort_length规定的前若干个字节进行排序。
字符串类型包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
以上没有说明的类型其实在某些场合也能够代替varchar、char，有兴趣可以查看官方文档。
3、日期和时间类型
DATETIME：
保存大范围的值。
封装格式：
'YYYY-MM-DDHH:
MM:
SS'。
——与时区无关，使用8字节存储空间，取值范围是：
'1000-01-0100:
00:
00'到'9999-12-3123:
59:
59'.
TIMESTAMP：
保存自1970年1月1日午夜（格林尼治标准时间）以来的秒数。
——使用4字节存储空间，取值范围是：
'1970-01-0100:
00:
01'UTC到'2038-01-1903:
14:
07'UTC，可以通过函数UNIX_TIMESTAMP（）转换为整数类型
效率方面考虑
MySql内置了传统时间和UNIX时间的互换函数，分别为
UNIX_TIMESTAMP（datetime）
FROM_UNIXTIME（unixtime）
比如运行SELECTUNIX_TIMESTAMP（'2010-03-0100:
00:
00'）
返回1267372800
运行SELECTFROM_UNIXTIME（1267372800）
返回'2010-03-0100:
00:
00'
于是，我们可以将时间字段里的数据替换为整型的UNIX时间，这样，比较时间就成为整数比较了，建立索引后能大大提高效率。
二、表结构调整
这里列出一些常用的调整表结构的命令：
1：
删除列
ALTERTABLE【表名字】DROP【列名称】
2：
增加列
ALTERTABLE【表名字】ADD【列名称】INTNOTNULLCOMMENT'注释说明'
3：
修改列的类型信息
ALTERTABLE【表名字】CHANGE【列名称】【新列名称（这里可以用和原来列同名即可）】BIGINTNOTNULLCOMMENT'注释说明'
4：
重命名列
ALTERTABLE【表名字】CHANGE【列名称】【新列名称】BIGINTNOTNULLCOMMENT'注释说明'
5：
重命名表
ALTERTABLE【表名字】RENAME【表新名字】
6：
删除表中主键
AlterTABLE【表名字】dropprimarykey
7：
添加主键
ALTERTABLEsj_resource_chargesADDCONSTRAINTPK_SJ_RESOURCE_CHARGESPRIMARYKEY（resid,resfromid）
8：
添加索引
ALTERTABLEsj_
升级会员 