MySQL日常操作V15.docx

1、MySQL日常操作V15HISUNMySQL日常操作Version 1.5BaiYaJun2014/1/10根据项目实际情况，记录MySQL数据库日常开发、维护经常用到的MySQL知识点和操作方法，适用于开发及维护人员。目录一、常用数据类型及操作 11、数值类型 11) 整数类型(Integer) 12) 定点类型(Fixed-Point) 13) 浮点类型(Floating-Point) 24) Bit类型(Bit-Value) 22、字符串型 21) Varchar 32) Char 33) Text 33、日期和时间类型 4二、表结构调整 4三、MySQL数据库导出/导入 61、mysq

2、ldump、mysql工具 62、导出/导入csv文件 71) 导出csv文件 72) 导入csv文件 7四、权限相关 81、服务器启动及首次root登录： 82、查看当前系统中的用户信息 83、密码设置 94、远程访问 105、创建数据库及用户 12五、使用XtraBackup对MySQL备份/恢复 151、XtraBackup2.0.7安装 152、全量备份/恢复 173、增量备份/恢复 18六、MySQL-REPLICATION安装步骤 191、软件下载及安装 192、主机配置 213、从机配置 224、数据同步 225、故障切换 236、配置KeepAlived 27附录： 30一、导

3、入数据到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.020130503 初稿1.1201305

4、03 1.520140110 增加了MySQL的REPLICATION以及备份恢复等内容，增补附录内容，其它各处有少量修改和完善一、常用数据类型及操作1、数值类型MySQL支持所有标准SQL数值数据类型。这些类型包括精确数值数据类型（INTEGER，SMALLINT，DECIMAL, 和NUMERIC)，以及近似数值数据类型（FLOAT，REAL和DOUBLE PRECISION）。关键字INT是INTEGER的同义词，关键字DEC和FIXED是DECIMAL的同义词。 MySQL将DOUBLE视为DOUBLE PRECISION（a nonstandard extension）的同义词。在未

5、开启REAL_AS_FLOAT模式的情况下，MySQL还将REAL视为DOUBLE PRECISION（a nonstandard variation）的同义词。BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和NDBCLUSTER表。1) 整数类型(Integer)整数类型包括INTEGER, INT, SMALLINT, TINYINT,MEDIUMINT, BIGINT。MySQL支持SQL标准的整数类型INTEGER或INT和SMALLINT。作为SQL标准的扩展，MySQL也支持整数类型TINYINT，MEDIUMINT和BIGINT。下面的表显示了每个整

6、数类型需要的存储和范围：MySQL整型数据基本包括以上几种，在设计字段类型时，可根据字段的长度，适当选择以上合适的数据类型：对于一般自增长的主键（数据量不是非常大），使用INT就可以，对于详单类的数据主键（数据量可能非常大的），建议使用BIGINT，对于只有少量可选数据的字段，如性别、状态等，可使用TINYINT(1)或TINYINT(2),一般都能满足需求了。这里要注意的就是数据类型的长度，不要把超过类型长度的数字赋给这个类型的字段2) 定点类型(Fixed-Point)该类型包括DECIMAL, NUMERIC。在MySQL中，NUMERIC是DECIMAL的同义词，用于存储必须为确切精度

7、的数据值，例如货币数据。MySQL5.5使用二进制格式保存DECIMAL。当声明该类型的列时，可以(并且通常要)指定精度(precision)和标度(scale)；例如：salary DECIMAL(5,2)在该例子中，5是精度，2是标度。精度表示保存值的主要位数，标度表示小数点后面可以保存的位数，该类型可以表示的数据范围为-999.99至999.99在标准SQL中，DECIMAL(M)等价于DECIMAL(M, 0)。类似地，DECIMAL等价于DECIMAL(M, 0)，这里M的值由具体的实现来决定，MySQL同时支持这两种格式的语法，并且默认的M的值为10。DECIMAL类型所能表示的数

8、据的最大位数为65，但是一个给定DECIMAL列的实际范围由指定的列的精度和小数位数来共同约束。DECIMAL或NUMERIC的最大位数是65，但具体的DECIMAL或NUMERIC列的实际范围受具体列的精度或标度约束。如果分配给此类列的值的小数点后位数超过指定的标度允许的范围，值将按该标度进行转换。(具体操作与操作系统有关，一般结果均被截取到允许的位数)。3) 浮点类型(Floating-Point)FLOAT和DOUBLE类型表示近似值的数据类型。在MySQL中，使用四个字节来表示单精度值，而使用八个字节来表示双精度值。这两种类型都是非精确的类型，经过一些操作后并不能保证运算的正确性，例如

9、M*G/G不一定等于M，虽然数据库内部算法已经使其尽可能的正确，但是结果还会有偏差。总之，对于精确度要求比较高时，建议使用DECIMAL来存储，DECIMAL在MySQL内存是以字符串存储的！ 4) Bit类型(Bit-Value)我们用的不多，不做详细阐述，这里把官方的类型说明搬过来参考：2、字符串型目前我们用得最多的是varchar和char，偶尔会用到text所以这里只描述这三种1) Varchar（1） 保存可变长字符串。比固定长度占用更少的存储空间，因为它只占用自己需要的空间。例外情况：使用ROW_FORMAT=FIXED创建的MyISAM表，它为每行使用固定长度的空间，可能会造成浪

10、费。（2） 存储长度信息。如果定义的列小于或等于255，则使用1个字节存储长度值，假设使用latin1字符集，如varchar（10）将占用11个字节的存储空间。反过来，varchar（1000），则占用1002个字节的存储空间。（3）节约空间，对性能有帮助。（4）5.0版本以上，无论是取值还是保存，MySQL都会保留字符串末尾的空格。只分配真正需要的空间使用varchar(5)和varchar(200)保存hello占用空间是一样的。这里应该指的是磁盘上的空间。那么使用较短列有何优势？巨大的优势较大的列会使用更多的内存，因为MySQL通常会分配固定大小的内存块（如varchar(200)会用

11、200个字符大小的内存空间）来保存值(然后对值进行trim操作，最后放入磁盘)或取值。这对排序或使用基于内存的临时表尤其不好。2) Char（1） 固定长度。（2） 保存值时，去掉末尾的空格。char常用于很短字符串或长度近似相同的字符串的时候很有用。如存储用户密码的MD5哈希值，它的长度总是一样的。char优于varchar的地方？1 对于经常改变的值，char优于varchar，因为固定长度行不容易产生碎片。当最长长度远大于平均长度，并且很少发生更新的时候，通常适合使用varchar。2对于很短的列，char的效率也是高于varchar的。如对于单字节字符集（如latin1)，char(1

12、)只会占用1个字节，而varchar(2)会占用2个字节（有一个字节用来存储长度的信息）。3) Text用于保存大量数据。（1） InnoDB在它们较大的时候会使用“外部”存储区域来进行保存。所以需要足够的外部存储空间来保存实际的值。（2） 排序方式不同于其他字符类型，不会按照完整长度进行排序，而只是按照max_sort_length规定的前若干个字节进行排序。字符串类型包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。以上没有说明的类型其实在某些场合也能够代替varchar、char，有兴趣可以查看官方文档。3、日期和时间类型DATETIM

13、E：保存大范围的值。封装格式：YYYY-MM-DD HH:MM:SS。与时区无关，使用8字节存储空间，取值范围是：1000-01-01 00:00:00到 9999-12-31 23:59:59.TIMESTAMP：保存自1970年1月1日午夜（格林尼治标准时间）以来的秒数。使用4字节存储空间，取值范围是：1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC，可以通过函数UNIX_TIMESTAMP()转换为整数类型效率方面考虑MySql内置了传统时间和UNIX时间的互换函数，分别为UNIX_TIMESTAMP(datetime)FROM_UNIX

14、TIME(unixtime)比如运行SELECT UNIX_TIMESTAMP(2010-03-01 00:00:00)返回1267372800运行SELECT FROM_UNIXTIME(1267372800)返回2010-03-01 00:00:00于是，我们可以将时间字段里的数据替换为整型的UNIX时间，这样，比较时间就成为整数比较了，建立索引后能大大提高效率。二、表结构调整这里列出一些常用的调整表结构的命令：1：删除列ALTER TABLE 【表名字】 DROP 【列名称】2：增加列ALTER TABLE 【表名字】 ADD 【列名称】 INT NOT NULL COMMENT 注释说

15、明3：修改列的类型信息ALTER TABLE 【表名字】 CHANGE 【列名称】 【新列名称（这里可以用和原来列同名即可）】 BIGINT NOT NULL COMMENT 注释说明4：重命名列ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】 BIGINT NOT NULL COMMENT 注释说明5：重命名表ALTER TABLE 【表名字】 RENAME 【表新名字】6：删除表中主键Alter TABLE 【表名字】 drop primary key7：添加主键ALTER TABLE sj_resource_charges ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid)8：添加索引ALTER TABLE sj_