shell脚本链接mysql模板如下:
mysql -h$hostname -P$port -u$username -p$password << EOF
use $database;
XXXXsql语句
commit;
EOF
增删改查举例如下:
#!/bin/bash
hostname="ip"
port="3306"
username="aabb"
password="aabb"
database="aa"
tablename="aa_test_shell"
//创建表
mysql -h$hostname -P$port -u$username -p$password << EOF
use $database;
create table IF NOT EXISTS $tablename (unit varchar(10) NOT NULL COMMENT '日期格式YYYY-MM-DD', prov_name varchar(32) NOT NULL COMMENT '省份名',
prov_id varchar(32) NOT NULL COMMENT '省份id',region_type int(2) NOT NULL COMMENT '地区类型:0全国,1省,2市,3区县',time_type int(2) NOT NULL COMMENT '12345,年季月日周');
commit;
EOF
if [ $? -eq 0 ]
then
echo "create table success !"
fi
//插入
mysql -h$hostname -P$port -u$username -p$password << EOF
use $database;
INSERT INTO aa_test_shell (unit, prov_name, prov_id, region_type, time_type) VALUES ('test', 'test','test', 1, 2);
INSERT INTO aa_test_shell (unit, prov_name, prov_id, region_type, time_type) VALUES ('test1', 'tes1t','test1', 11, 12);
commit;
EOF
if [ $? -eq 0 ]
then
echo "insert success !"
fi
//删除
mysql -h$hostname -P$port -u$username -p$password << EOF
use $database;
delete from aa_test_shell where unit='test';
commit;
EOF
if [ $? -eq 0 ]
then
echo "delete success !"
fi
//更新
mysql -h$hostname -P$port -u$username -p$password << EOF
use $database;
update aa_test_shell set region_type=100 where unit='test1';
commit;
EOF
if [ $? -eq 0 ]
then
echo "update success !"
fi