mysql数据建库实战_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来试图恢复数据，下次接着聊吧，累了……

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

posted on

2008-11-04 16:47

一江水

阅读(514)

评论(0)

编辑

收藏