linux下mysql数据导入到redis
自Redis 2.6以上版本起,Redis支持快速大批量导入数据,即Pipe传输。通过将要导入的命令转换为Resp格式,然后通过MySQL的concat()来整理出最终导入的命令集合,以达到快速导入的目的。
1.建立测试表
CREATE TABLE `order` ( `orderid` varchar(38) DEFAULT NULL, `ordertime` datetime DEFAULT NULL, `ordermoney` decimal(20,0) DEFAULT NULL, `orderstatus` char(1) DEFAULT NULL, `version` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2.插入数据
INSERT INTO `order` VALUES ('1', '2018-7-29 00:06:04', 45, '1', 1); INSERT INTO `order` VALUES ('2', '2018-7-18 00:06:39', 46, '1', 2); INSERT INTO `order` VALUES ('3', '2018-7-3 00:06:50', 12, '1', 1);
3.编写查询脚本,并将该脚本保存成order.sql文件
SELECT CONCAT( '*10\r\n', -- *表示数组,10表示数组元素个数*,\r\n是规定分隔符,10这个数字根据下面红底字段数量进行调整 '$', LENGTH(redis_cmd), '\r\n', redis_cmd, '\r\n', --$表示长字符串,LENGTH(redis_cmd)表示字符串长度,redis_cmd字符串变量 '$', LENGTH(redis_key), '\r\n', redis_key, '\r\n', '$', LENGTH(hkey1),'\r\n',hkey1,'\r\n','$',LENGTH(hval1),'\r\n',hval1,'\r\n', '$', LENGTH(hkey2),'\r\n',hkey2,'\r\n','$',LENGTH(hval2),'\r\n',hval2,'\r\n', '$', LENGTH(hkey3),'\r\n',hkey3,'\r\n','$',LENGTH(hval3),'\r\n',hval3,'\r\n', '$', LENGTH(hkey4),'\r\n',hkey4,'\r\n','$',LENGTH(hval4),'\r\n',hval4,'\r' ) FROM ( SELECT 'HSET' AS redis_cmd, CONCAT('order:info:',orderid) AS redis_key, 'ordertime' AS hkey1, ordertime AS hval1, 'ordermoney' AS hkey2, ordermoney AS hval2, 'orderstatus' AS hkey3, orderstatus AS hval3, 'version' AS hkey4, `version` AS hval4 FROM `order` ) AS t
4.执行导入命令
mysql -h host -uroot -p123456 test --default-character-set=utf8 --skip-column-names --raw < /usr/redis/order.sql | /usr/redis/redis-cli -h host -p 6379 -a 123456 --pipe
#-h host -uroot -p123456 test 分别为:mysql远程地址,用户名,密码,数据库名
#/usr/redis/order.sql | /usr/redis/redis-cli 分别为sql文件和redis客户端文件目录的地址
#-h host -p 6379 -a 123456 分别为redis远程地址,端口,密码