mkrtb.sh

#!/bin/sh

# description
# the sample shell script for 
# this shell script is sample of creating mysql table with random number record.

# reference
# http://sasuke.main.jp/sqlcsv.html MySQL CSVファイル入出力
# http://blogs.yahoo.co.jp/airmikan/22712779.html LOAD DATA ほげほげ


numColumn=300000


if [ "$1" = "-cc" ] ; then

for i in `seq $numColumn`
do
	printf "\r exporting... \t\t$i/$numColumn"
	echo "$i,$((RANDOM%30000))" >>testtb.csv
done
echo "\a"
echo "created a csv file!"
exit 0

fi

if [ "$1" = "-ctb" ] ; then

MYSQL_PWD="******" mysql --local-infile=1 -u user -D db <<EOF
create table tb1 (id int, record int);
load data local infile './testtb.csv' into table tb1 fields terminated by ',' enclosed by '"' (id, record);
create table tb2 (id int, record int);
insert into tb2 select * from tb1;
alter table tb2 add index index_name(record);
EOF
echo "created a mysql table!"
exit 0

fi


if [ "$1" = "-dtb" ] ; then

MYSQL_PWD="******" mysql -u user -D db -e "drop table tb1, tb2"
echo "deleted table!"
exit 0

fi


if [ "$1" = "-rc" ] ; then

rm ./testtb.csv
echo "removed csv file!"
exit 0

fi

echo "Usege: sh mkrtb.sh [-cc|-ctb|-dtb|-rc]"
echo "\t -cc create random csv"
echo "\t -ctb create mysql table loading csv"
echo "\t -dtb delete table"
echo "\t -rc remove csv file"