shell提取msyql指定数据库下表创建语句为单文件

dbcn="mysql -h172.16.1.194 -uroot -p123456"; db=dsp_ad_center; ii=0; ct=`$dbcn -N -e " SELECT COUNT(1) FROM information_schema.TABLES AS t WHERE t.TABLE_SCHEMA = '$db' AND t.TABLE_TYPE = 'BASE TABLE' ; "`; mkdir -p /chenenhui/$db while true; do if [ $ii -lt $ct ] then p=$ii; let ii++; echo p=$p echo ii=$ii table_name=`$dbcn -N -e " SELECT CONCAT( t.TABLE_SCHEMA,'.',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_name sleep 0; ss=`$dbcn -N -e "SHOW CREATE TABLE $table_name ;"` echo -e "$ss" > /chenenhui/$db/create_table_help.tsv `$dbcn -e " CREATE TABLE IF NOT EXISTS test.create_table_help ( table_name VARCHAR(1500) DEFAULT '', table_script VARCHAR(1500) DEFAULT '', xh INT AUTO_INCREMENT,PRIMARY KEY(xh) ) CHARSET = utf8 ; truncate table test.create_table_help; LOAD DATA LOCAL INFILE '//chenenhui//$db//create_table_help.tsv' INTO TABLE test.create_table_help CHARACTER SET utf8;"` ss_script=`$dbcn -N -e "SELECT concat(CASE WHEN xh = 1 THEN concat('USE ','$db;',char(10) ) ELSE table_name END , table_script ) FROM test.create_table_help ;"` echo -e "$ss_script" > /chenenhui/$db/$table_name.sql echo ";" >> /chenenhui/$db/$table_name.sql echo $table_name else echo '_while finished'; rm /chenenhui/$db/create_table_help.tsv `$dbcn -N -e "DROP TABLE IF EXISTS test.create_table_help;"` exit 0; fi done