MySQL数据库操作实战

昨天项目发布，要做数据移行，要实现的功能很变态，时间很紧迫，基本上是使出了全身解数，才能有快又准地完成工作，期间发现很多小技巧串联起来使用，效果的确非常好。

武器：
1 mysqldump+mysql命令=>数据的导入导出，备份恢复

2 perl命令行=>很多时候，你有一个想法，它就能只用一句话，帮你实现它，省得编写很多代码的麻烦

3 shell命令=>组合拳，单独的命令谁都会，组合起来使用，需要一些经验的积累

4 强大的编辑器支持UltraEdit/EmEdit等=>UltraEdit最专业，但不支持字符集转换；EmEdit支持字符集转换，且最新版有很多插件可供选择，基本够常见的操作了，对于WEB开发人员来说，常需要在GB2312、utf8或者其他国家语言euc-jp、shift-jis等等编码中转来转去，EmEdit的确是首选，特别赞的还有EmEdit的录制键盘动作并播放的功能

常见组合：
1 数据移行时：
mysqldump --opt -t -h locahost -P 23236 -p mydb table -w "id>27000" > table.sql
>输入密码
OK , mydb中的表table中id大于27000的所有数据列的INSERT语句就保存到table.sql中了。-h、-P、-p就不多说了，--opt 是mysqldump的最常用组合选项，可以认为是加速语句；-t 则很有用，表示--no-create-info，就是你不需要建表语句，只需要数据（INSERT语句）；-w就是where条件，也非常有用，让你有选择的导出数据。其他常见选项有：-d ： 只需要建表语句，不需要insert语句；-c ：给每个insert语句加上列名（field），默认时，insert语句是“insert into `table` values (1),(2),(3);”，而加上-c后，就变成了“insert into table(id) values (1),(2),(3);”。有什么用？一会儿自会明白。

如果表中数据非常多，导出insert语句就非常长（默认情况下，一个表的所有数据都在一个insert语句中，不换行），要查看这个文件常常引起死机，无论是linux下用vi，less查看，还是windows用EmEdit等编辑器，因为他们都是以行为单位load数据的，一行的数据过大就会内存占用过大，怎么办？

你发现，只需要在EmEdit里查找“),(”这个字符串，将它替换为"),\n("，也就是加个换行，就行了，于是就这么做，结果发现——当数据量达到上万时，EmEdit就像数羊一样一个一个替换，等它换完，你都睡了一觉了！！

怎么办呢？你当然知道写个脚本就行了，很容易，但是花的时间多，还需要测试，有简单办法吗？恩，那就轮到perl单命令行上场了：
perl -i.bak -pe 's/\),\(/\),\n\(/g' table.sql
OK，替换结束，且生成了一个备份文件table.sql.bak，如果你发现写错了，效果不对，还有救：）

当然了，如果你会用sed、awk等强大的编辑工具，这个也是小case，这里就不多说了。

2 数据导入
导出的数据经过处理，就要导入目标数据库，现在有一个表，字段非常多，其中主键为id，导入另一个数据库的相同表里，但是id不想直接插入，而是只要数据，id最好是自动生成的，跟在目标数据表最末一条数据之后即可，以免主键冲突。怎么搞呢？
这时候就能用上刚才说到的-c选项了，加了-c，导出的数据有field列，只需要把其中的id列都替换为空即可：）
这个替换过程，用EmEdit或上面说到的perl命令行都可以，只是速度的区别了。

3 接上面的问题，老数据导入了新的表，生成了一串新的连续id，但和老数据完全不一样了，现在有其他几张表中使用了老数据的id作为外键，要把它们统一改为新的id。例如：
老数据中table.id=10010，有一个表table2.table=10010,是外键关系，要把这个table2.table改为新数据表中自动产生的那个id，就比如说是6041.现在手头上的数据只有老id列表，且知道插入新表中的id是自增的，每次加1，从6041开始，怎么快速修改 table2，table3中的相应外键呢？
一个文件：
10010
10201
11301
11499
……

要替换成：
update table2 set table=6041 where id=10010;
update table2 set table=6042 where id=10201;
update table2 set table=6043 where id=11301;
update table2 set table=6044 where id=11499;
……

我们可以这么组合手头的工具：
perl -i.bak -pe 'BEGIN {$x=6041} s/^(\d+)/update table2 set table=$x where id=$1;/ ; $x++' file.txt
OK，搞定
这个例子的特色其实就是perl命令行中的BEGIN的用法，有begin，当然有end，具体就自己查查文档吧：）

4 还有EmEdit中的录制键盘动作并播放也很常用，毕竟写正则比较费脑子，用工具虽然处理速度慢一些，但是思考速度要快很多。今天碰到一件事，需要把服务器上某个目录中的bmp图片都转为jpg格式，也是要使用组合拳的，其中就用到了EmEdit的录制播放功能。
已知某目录下有N多bmp图片，通过convert命令可以转格式（安装Image::Magick模块即可）
怎么搞？
find ./ -name "*.bmp" > bmp.txt
先拿到文件列表再说。

在EmEdit中打开该列表，一行行文件名。
你发现你想做的就是：
./dir1/dir2/file1.bmp
./dir1/dir2/file2.bmp
./dir1/dir2/file3.bmp
转变为：
convert ./dir1/dir2/file1.bmp ./dir1/dir2/file1.jpg
convert ./dir1/dir2/file2.bmp ./dir1/dir2/file2.jpg
convert ./dir1/dir2/file3.bmp ./dir1/dir2/file3.jpg

这是一个非常有规律的动作，在行首，按shift+End（选择），ctrl+c（复制），End（到行尾），空格，ctrl+v（粘贴），backspace三次，输入jpg，HOME到行首，输入convert空格，下一行，HOME到行首
只要把这个操作序列记录下来，对每行都是一样操作，这时就可以使用EmEdit的录制+播放功能了，录制完成后，按F4快捷键，一爽到底~~yeah~~

运行之后要删除原图：
在shell下：
find ./ -name "*.bmp" | xargs -n1 rm -f
(找到本目录下的bmp图片文件名列表，一个一个传给rm -f，删之，慎用)

5 数据库恢复（从bin-log中恢复数据库也是一个非常重要的技巧），出现误操作或DB服务器不幸意外丢失数据时，常常要查bin-log来试图恢复数据，下次接着聊吧，累了……

呵呵，乱写写，好像不只是数据库操作了，不过挺实在，欢迎各位拍砖吧

评论

15 楼 0000 2008-12-08  

我最近觉得 PsPad 这个小东西免费又好用，UE之类的想买都找不到人付钱

14 楼 harvey415 2008-11-29  

流浪者A 写道

能不能把字调大点啊，看得好辛苦~~

Ctrl+鼠标滚轴

13 楼 zhoujj303030 2008-11-27  

虽然现在还没有真正用到过这些组合命令，但是我想还是先收藏起来再说！学习学习，以备以后使用！

12 楼 abo 2008-11-05  

一天之内要做这么多事，老板也太狠心了点。

11 楼 流浪者A 2008-11-02  

能不能把字调大点啊，看得好辛苦~~

10 楼 renyanwei 2008-10-23  

恩 不错 支持一下哈

9 楼 touya 2008-10-20  

tedeyang 写道

ultraedit的字符集能力很强,不会不够用的,至于录像之类的宏操作,那是小儿科了.
楼主又要perl,又要shell,又要ultraedit,你是什么操作系统?
貌似用windows+cygwin?

我所说的字符集转换比较特殊，是在简体中文、日文euc/s-jis/utf8等等之间转换，UltraEdit原生支持的字符集似乎很少，LS是否装了插件？
至于操作系统，我们用的架构是服务器上安装Samba，虚拟出共享盘来，这样就可以在windows上编辑文件，在服务器上运行了。不过本机的确也安装了cygwin，的确是不错的选择。

8 楼 tedeyang 2008-10-20  

ultraedit的字符集能力很强,不会不够用的,至于录像之类的宏操作,那是小儿科了.
楼主又要perl,又要shell,又要ultraedit,你是什么操作系统?
貌似用windows+cygwin?

7 楼 vdgame 2008-10-18  

touya 写道

UltraEdit最专业，但不支持字符集转换；

不是吧，我一直用UltraEdit转换字符集

6 楼 alexwan 2008-10-14  

不错，写的不错

5 楼 ai2ming 2008-10-13  

第三个生成update语句的问题,我在类似情况下,是使用UE的列模式和EXCEL的自动填充完成.

4 楼 disillusion 2008-10-12  

恩，很强，方法也很实用！！尝试一下！

3 楼 korpton 2008-10-11  

赞一个，那是钢钢的

2 楼 bearice 2008-10-11  

至于emeditor那段……直接用正则表达式替换就行了吧

1 楼 jieyuan_cg 2008-10-10  

恩，有同样的经历……但这些东西，最好都留下文档，或者把操作文档留下来，下次迁移，将会帮上大忙。

呵呵，不同数据库之前迁移数据，还是推荐用Kettle，很好很强大！