1.2 批量生成MySQL建表语句

MySQL数据库服务器为Linux系统
数据库用户名：root
数据库密码：123456
数据库库名：test

一、 要求

将test库所有表的建表SQL脚本生成到服务器“/opt/tables”目录，CREATE之前加DROP TABLE IF EXISTS语句，若表存在则先删表，一个表对应一个脚本，以表名命名对应文件。

二、 实现方法

创建一个shell脚本执行即可，脚本内容如下。

#!/bin/bash
### 数据库连接，用户名:root, 密码:root
dbcn='mysql -uroot -p123456';  

#### 数据库名
db=test;  

### 获取数据库中表个数
ct=`$dbcn -N -e " SELECT COUNT(1)  FROM information_schema.TABLES AS t WHERE t.TABLE_SCHEMA = '$db' AND t.TABLE_TYPE = 'BASE TABLE' ; "`;  
echo '数据库表总数量：'$ct;
## shell 文件所在目录
workdir=$(cd $(dirname $0); pwd);

## 创建建表语句存放文件夹
sqldir=/opt/tables;
mkdir -p $sqldir;


# 遍历数据库中的所有表
ii=0; 
while [ $ii -lt $ct ];
do  
  p=$ii;  
  let ii++;  
  echo '----------------第'$ii'张表------------------';
                    
  ## 获取表名
    table_name=`$dbcn -N -e " SELECT t.TABLE_NAME FROM information_schema.TABLES AS t  where  t.TABLE_SCHEMA = '$db' AND t.TABLE_TYPE = 'BASE TABLE' limit $p,1;"`;  
    echo '表名：'$table_name;

  ## 获取建表语句
    table_ddl=`$dbcn $db -N -e "SHOW CREATE TABLE  $table_name ;"`;
    echo '建表语句：'${table_ddl#*${table_name}};
                                                
     ## 根据表名，把建表语句放入指定文件中
    echo -e "DROP TABLE IF EXISTS ${table_name};" > $sqldir/${table_name}.sql;                                                   
    echo -e "${table_ddl#*${table_name}};" >> $sqldir/${table_name}.sql ;
done
echo '数据库建表语句导出完毕!';

三、其他要求实现

如表的字符集为utf8，排序规则为utf8_bin（字符比较时大小写敏感），建表语句和字符串字段后会跟utf8和utf8_bin。

DROP TABLE IF EXISTS TEST_INST;
CREATE TABLE `TEST_INST` (
  `INST_CODE` VARCHAR(32) COLLATE utf8_bin DEFAULT NULL COMMENT '机构代码',
  `INST_ATTRIBUTE` VARCHAR(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '机构属性',
  KEY `INDEX_TEST_INST_1` (`INST_CODE`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

1、 需要把所有建表脚本字段后跟的utf8和utf8_bin去掉，只留表后的字符属性，字段属性跟随表。
shell脚本如下（具体要替换哪些字符串根据实际情况修改）

#!/bin/bash 
for file_name in `grep  "COLLATE utf8_bin" -rl /opt/tables`
   do
    # echo $file_name    
     sed -i 's#COLLATE utf8_bin##' $file_name;
 done

2、 表后的字符排序属性utf8_bin需要批量修改成utf8_general_ci
shell脚本如下

#!/bin/bash 
for file_name in `grep  "utf8_bin" -rl /opt/tables`
   do
    # echo $file_name    
     sed -i 's#utf8_bin#utf8_general_ci#' $file_name;
 done

3、 表后的属性去掉，跟随数据库的默认属性。
shell脚本如下

#!/bin/bash 
for file_name in `grep  "ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin" -rl /opt/tables`
   do
    # echo $file_name    
     sed -i 's#ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin##' $file_name;
 done