读写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
就会导致在一次函数调用后因为原有管道被关闭而终止运行。