将MySql上的数据导入到Redis中

女爷i 2022-03-31 12:42 1200阅读 0赞

> 一，问题

这段时间学完了Redis，因为Redis是基于内存的数据库，它查询数据的速度比查询mysql要快，因此，下一步，我就需要将mysql上的数据导入到Redis中。

> 二，解决方案

2.1 mysql数据导入redis的原理

利用redis持久化方式中的AOF方式，我们将mysql的数据查询出来，同时让数据与一些符号进行拼接，得到一个类似AOF文件的内容。我们将该内容交给redis去执行，那样子数据就能导入进去了。

2.2 实现过程

①数据库表

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhb3RpYW8wMTk5_size_16_color_FFFFFF_t_70]

②将数据库表中数据与其他符号进行拼接，仿造AOF文件的格式

建立一个user.sql文件，文件内容如下：

SELECT CONCAT(  
      "*12\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\n'
      '$', LENGTH(hkey4), '\r\n',  hkey4, '\r\n','$', LENGTH(hval4), '\r\n',  hval4, '\r\n'
      '$', LENGTH(hkey5), '\r\n',  hkey5, '\r\n','$', LENGTH(hval5), '\r\n',  hval5, '\r'
    )  
    FROM (  
      SELECT  
      'HMSET' as redis_cmd,  
      CONCAT('userID:' ,userID) AS redis_key,  
      'userName' AS hkey1, userName AS hval1,
      'password' AS hkey2, password AS hval2,
      'nickName' AS hkey3, if(nickName is not null, nickName, '') AS hval3,  
      'userFace' AS hkey4, if(userFace is not null, userFace, '') AS hval4,  
      'userType' AS hkey5, userType AS hval5
      FROM user  
    ) AS t

user.sql文件开头中的12，我百度了一下，有人说这是表示这个数组的长度为12，而这个数组是用来存储数据的。大家可以数一下符号$的个数，刚好与12相同。

③将上面的user.sql文件放到linux系统下

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhb3RpYW8wMTk5_size_16_color_FFFFFF_t_70 1]

④执行linux命令去执行user.sql文件，导入数据

mysql -uroot -p123 -hwww.baidu.com testmysql  --default-character-set=utf8 
     --skip-column-names --raw < user.sql  |  redis-cli --pipe -a 123

部分命令解释：
    ①mysql -uroot -p123 -hwww.baidu.com testmysql
    链接mysql数据库
    mysql的账号为root
    mysql的密码为123
    mysql服务器的IP地址为www.baidu.com
    mysql中的数据库名称为testmysql
    
    ②--default-character-set=utf8
    默认使用utf8编码
    
    ③ --skip-column-names --raw
    让MySQL不输出列名
    
    ④redis-cli --pipe -a 123
    使用管道将拼接好的数据传送过去Redis：redis-cli --pipe
    Redis的登录密码：123

> 三，总结遇到的一些坑

3.1 mysql用户名和密码不能与前面的字符用空格分开，否则会报错

①错误的方式

错误演示：mysql -u  root -p  123 -hwww.baidu.com testmysql

ERR unknown command `mysql`, with args beginning with: `Ver`, `14.14`, `Distrib`, `5.7.23,`, `for`, `Linux`, `(x86_64)`, `using`, `EditLine`, `wrapper`, 
    ERR unknown command `Copyright`, with args beginning with: `(c)`, `2000,`, `2018,`, `Oracle`, `and/or`, `its`, `affiliates.`, `All`, `rights`, `reserved.`, 
    ERR unknown command `Oracle`, with args beginning with: `is`, `a`, `registered`, `trademark`, `of`, `Oracle`, `Corporation`, `and/or`, `its`, 
    ERR unknown command `affiliates.`, with args beginning with: `Other`, `names`, `may`, `be`, `trademarks`, `of`, `their`, `respective`, 
    ERR unknown command `owners.`, with args beginning with: 
    ERR unknown command `Usage:`, with args beginning with: `mysql`, `[OPTIONS]`, `[database]`, 
    ERR unknown command `-?,`, with args beginning with: `--help`, `Display`, `this`, `help`, `and`, `exit.`, 
    ERR unknown command `-I,`, with args beginning with: `--help`, `Synonym`, `for`, `-?`, 
    ERR Protocol error: unbalanced quotes in request

②正确的方式

正确方式：mysql -uroot -p123 -hwww.baidu.com testmysql

3.2 编写的sql脚本，不能带查询条件，否则无法导入

①错误的方式

All data transferred. Waiting for the last reply...
    Last reply received from server.
    errors: 0, replies: 0

②正确的方式

至于为什么添加了where语句之后就无法查询出数据，这个原理我不是很理解，但是我将这个脚本放到mysql上去查询又没有问题。有点懵逼。现在先总结一下，下次有时间再去探究探究。

3.3 编写的sql脚本中，数组的长度不能太短，否则会出错

①错误的方式。这里数组长度只有为4。不够装下所有元素

SELECT CONCAT(  
      "*4\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\n'
      '$', LENGTH(hkey4), '\r\n',  hkey4, '\r\n','$', LENGTH(hval4), '\r\n',  hval4, '\r\n'
      '$', LENGTH(hkey5), '\r\n',  hkey5, '\r\n','$', LENGTH(hval5), '\r\n',  hval5, '\r'
    )  
    FROM (  
      SELECT  
      'HMSET' as redis_cmd,  
      CONCAT('userID:' ,userID) AS redis_key,  
      'userName' AS hkey1, userName AS hval1,
      'password' AS hkey2, password AS hval2,
      'nickName' AS hkey3, if(nickName is not null, nickName, '') AS hval3,  
      'userFace' AS hkey4, if(userFace is not null, userFace, '') AS hval4,  
      'userType' AS hkey5, userType AS hval5
      FROM user  
      where userId = "aaa" and password = "123"
    ) AS t

mysql: [Warning] Using a password on the command line interface can be insecure.
    All data transferred. Waiting for the last reply...
    ERR unknown command `$3`, with args beginning with: 
    ERR unknown command `bId`, with args beginning with: 
    ERR unknown command `$4`, with args beginning with: 
    ERR unknown command `ggg3`, with args beginning with: 
    ERR unknown command `$3`, with args beginning with: 
    ERR unknown command `bId`, with args beginning with: 
    ERR unknown command `$4`, with args beginning with: 
    ERR unknown command `ggg4`, with args beginning with: 
    ERR unknown command `$3`, with args beginning with: 
    ERR unknown command `bId`, with args beginning with: 
    ERR unknown command `$2`, with args beginning with: 
    ERR unknown command `tt`, with args beginning with: 
    Last reply received from server.

②正确的方式。数一下符号$的个数，然后将数组改成这个数即可。

以上内容参考几位前辈：  
[https://blog.csdn.net/weixin\_37782390/article/details/78909131][https_blog.csdn.net_weixin_37782390_article_details_78909131]  
[https://blog.csdn.net/fyihdg/article/details/79237239][https_blog.csdn.net_fyihdg_article_details_79237239]  
[https://blog.csdn.net/qq\_32647893/article/details/82728618][https_blog.csdn.net_qq_32647893_article_details_82728618]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhb3RpYW8wMTk5_size_16_color_FFFFFF_t_70]: /images/20220331/af8171a87f4a44dfbb360827e400c6b1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhb3RpYW8wMTk5_size_16_color_FFFFFF_t_70 1]: /images/20220331/b534f275476b46f58061c37b668ffc3c.png
[https_blog.csdn.net_weixin_37782390_article_details_78909131]: https://blog.csdn.net/weixin_37782390/article/details/78909131
[https_blog.csdn.net_fyihdg_article_details_79237239]: https://blog.csdn.net/fyihdg/article/details/79237239
[https_blog.csdn.net_qq_32647893_article_details_82728618]: https://blog.csdn.net/qq_32647893/article/details/82728618