mysql数据导入到redis,将mysql数据导入到redis

用脚本模拟redis的客户端redis-cli实现：(注：mysql地址为10.86.30.203 redis地址为10.86.31.50)

1.首先确定mysql表的列：

desc test；

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| test1 | varchar(50) | YES | | NULL | |

| test2 | varchar(64) | YES | | NULL | |

| test3 | int(11) | YES | | NULL | |

+-----------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

2.编写sql脚本：

vi /home/darren/test.sql

SELECT CONCAT(

"*8\r\n",

‘$‘, LENGTH(redis_cmd), ‘\r\n‘,redis_cmd, ‘\r\n‘,‘$‘, 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‘)

FROM (

SELECT

‘HMSET‘ AS redis_cmd, CONCAT(‘id:‘,id) AS redis_key,

‘test1‘ AS hkey1,test1 AS hval1,

‘test2‘ AS hkey2,test2 AS hval2,

‘test3‘ AS hkey3,test3 AS hval3

FROM test

) AS t

注意：其中*8的意思是一共有8个参数，$length()为参数的长度。脚本中test1，test2，test3均为mysql的列名，可自行修改。

3.执行导入命令：

mysql -uroot -pxxx -h 10.86.30.203 test --skip-column-names --raw < /home/darren/test.sql | redis-cli --pipe

----------------------------------------------------------------------------------------------------------------

mysql: [Warning] Using a password on the command line interface can be insecure.

All data transferred. Waiting for the last reply...

Last reply received from server.

errors: 0, replies: 1

出现以上errors为0为导入成功。

–raw: 使mysql不转换字段值中的换行符。–skip-column-names: 使mysql输出的每行中不包含列名。