读写MySQL
背景说明
很多时候我们要统计的目标数据并不存在于文本文件中，而往往是保存在数据库中，并且为了与展示程序做对接，最终的数据也需要写回到数据库中。
假设我们有如下一张存储了每天股票交易信息的数据表：
mysql> show create table rank_201507\G
*************************** 1. row ***************************
Table: rank_201507
Create Table: CREATE TABLE `rank_201507` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`collect_day` varchar(8) NOT NULL DEFAULT '' COMMENT '采集日期',
`collect_hour` varchar(2) NOT NULL DEFAULT '' COMMENT '采集时间点',
`board_name` varchar(20) NOT NULL DEFAULT '' COMMENT '排行榜名称',
`seq` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '排序字段',
`stockid` varchar(20) NOT NULL DEFAULT '' COMMENT '股票代码',
`stock_name` varchar(100) NOT NULL DEFAULT '' COMMENT '股票名称',
`status` tinyint(4) NOT NULL DEFAULT '0',
`price` float NOT NULL DEFAULT '0' COMMENT '最新价',
`price_change` float NOT NULL DEFAULT '0' COMMENT '涨跌幅',
`trading_volume` int(11) NOT NULL DEFAULT '0' COMMENT '成交量（手）',
`trading_amount` float NOT NULL DEFAULT '0' COMMENT '成交额（万）',
`change_rate` int(11) NOT NULL DEFAULT '0',
`small_deal` int(11) NOT NULL DEFAULT '0' COMMENT '小单',
`medium_deal` int(11) NOT NULL DEFAULT '0' COMMENT '中单',
`big_deal` int(11) NOT NULL DEFAULT '0' COMMENT '大单',
`huge_deal` int(11) NOT NULL DEFAULT '0' COMMENT '超大单',
`exposure_amount` int(11) NOT NULL DEFAULT '0' COMMENT '历史媒体曝光量',
`exposure_amount_recent` int(11) NOT NULL DEFAULT '0',
`trend_up` int(11) NOT NULL DEFAULT '0' COMMENT '看涨数量',
`trend_down` int(11) NOT NULL DEFAULT '0' COMMENT '看跌数量',
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `hour_stock` (`collect_day`,`collect_hour`,`board_name`,`stockid`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=248381 DEFAULT CHARSET=utf8
mysql> select * from rank_201507 limit 1\G
*************************** 1. row ***************************
id: 1
collect_day: 20150701
collect_hour: 07
board_name: Raise
seq: 1
stockid: sza-300231
stock_name: 银信科技
status: 1
price: 12.38
price_change: 10.04
trading_volume: 29731
trading_amount: 3639.45
change_rate: 0
small_deal: 524
medium_deal: -554
big_deal: -107
huge_deal: 137
exposure_amount: 175582
exposure_amount_recent: 187
trend_up: 29731
trend_down: 5885
update_time: 2015-07-01 07:10:07
1 row in set (0.06 sec)
现在要统计7月21日那一天大单数量超过50%的股票数据，并保存到新的数据表中stock_list中：
mysql> show create table stat.stock_list\G
*************************** 1. row ***************************
Table: stock_list
Create Table: CREATE TABLE `stock_list` (
`code` varchar(255) NOT NULL DEFAULT '',
`name` varchar(255) NOT NULL,
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
问题分析
因为awk并没有jdbc的访问驱动，所以需要借助于mysql命令行的方式来完成数据的访问和写入。
解决方案
# 定义数据库记录插入函数
function insertValue(code, name){
SQL_HEAD="/usr/bin/mysql -uroot -p123456 -e "
TABLE_OP="insert ignore into stat.stock_list set "
CODE = "code = \047"code"\047, "
NAME = "name = \047"name"\047"
comm = SQL_HEAD "\"" TABLE_OP "" CODE "" NAME "\"";
print cmd;
comm|getline result;
close(comm);
}
BEGIN{
# 查询sql语句
sql = "select stockid, stock_name, abs(small_deal), abs(medium_deal), abs(big_deal), abs(huge_deal) from stock.rank_201507 where collect_day = '20150721'";
cmd = "/usr/bin/mysql -uroot -p123456 -N -e '" sql "'";
# 使用mysql命令行执行
while( cmd| getline > 0 ) {
total = $3 + $4 + $5 + $6;
# 判断条件满足插入数据库记录
if ( total > 0 && $5/total > 0.5 ) {
print $1
insertValue($1, $2);
}
}
close(cmd)
}
$awk -f case_4.awk
mysql> select * from stat.stock_list limit 10; +------------+--------------+ | code | name | +------------+--------------+ | sha-600301 | ST南化 | | sha-600888 | 新疆众和 | | sza-300236 | 上海新阳 | | sha-601678 | 滨化股份 | | sza-300353 | 东土科技 | | sha-600066 | 宇通客车 | | sha-600396 | 金山股份 | | sza-002195 | 海隆软件 | | sza-002327 | 富安娜 | | sza-002246 | 北化股份 | +------------+--------------+ 10 rows in set (0.00 sec)
需要注意的有2点：
- 单引号“'”需要转义成
\047; - 注意BEGIN中的命令变量是
cmd，函数insertValue()中的命令遍历是comm，这是因为awk中的变量都是全局的，如果都用成cmd就会导致在一次函数调用后因为原有管道被关闭而终止运行。