win mysql导入redis_Windows 下 MySQL 数据导入 Redis

最近正式开始学习了Redis的使用，于是就在思考mysql的数据要怎么样导入到redis中呢？于是查询资料：资料链接如下：

结果如下：

1.sql脚本，数据就自行添加吧。

CREATE TABLE `xb_cp_name` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '渠道类别与产品类别关系',

`Description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关系描述',

`Status` tinyint(4) NOT NULL DEFAULT 1 COMMENT '状态',

`Sort` int(11) NOT NULL DEFAULT 999 COMMENT '权重状态',

`IsDel` tinyint(4) NOT NULL DEFAULT 0 COMMENT '删除标志',

`OperatorID` int(4) NULL DEFAULT NULL COMMENT '操作者ID',

`UpdateTime` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',

PRIMARY KEY (`ID`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2.导入数据到redis脚本。

SELECT CONCAT(

"*18\n",

'$',LENGTH(redis_cmd),'\n',redis_cmd,'\n',

'$',LENGTH(redis_key),'\n',redis_key,'\n',

'$',LENGTH(hkey1),'\n',hkey1,'\n','$',LENGTH(hval1),'\n',hval1,'\n',

'$',LENGTH(hkey2),'\n',hkey2,'\n','$',LENGTH(hval2),'\n',hval2,'\n',

'$',LENGTH(hkey3),'\n',hkey3,'\n','$',LENGTH(hval3),'\n',hval3,'\n',

'$',LENGTH(hkey4),'\n',hkey4,'\n','$',LENGTH(hval4),'\n',hval4,'\n',

'$',LENGTH(hkey5),'\n',hkey5,'\n','$',LENGTH(hval5),'\n',hval5,'\n',

'$',LENGTH(hkey6),'\n',hkey6,'\n','$',LENGTH(hval6),'\n',hval6,'\n',

'$',LENGTH(hkey7),'\n',hkey7,'\n','$',LENGTH(hval7),'\n',hval7,'\n',

'$',LENGTH(hkey8),'\n',hkey8,'\n','$',LENGTH(hval8),'\n',hval8

)FROM(

SELECT 'HMSET' AS redis_cmd,

concat_ws(':','xb_cp_name', ID) AS redis_key,

'ID' AS hkey1, ID AS hval1,

'Name' AS hkey2, Name AS hval2,

'Description' AS hkey3, if(Description is not null, Description, ' ') AS hval3,

'Status' AS hkey4, Status AS hval4,

'Sort' AS hkey5, Sort AS hval5,

'IsDel' AS hkey6, IsDel AS hval6,

'OperatorID' AS hkey7, OperatorID AS hval7,

'UpdateTime' AS hkey8, UpdateTime AS hval8

From xb_cp_name

)AS t

3.上述解释

资料显示 自Redis 2.6以上版本起，Redis支持快速大批量导入数据，即官网的Redis Mass Insertion,即Pipe传输，通过将要导入的命令转换为Resp格式，然后通过MySQL的concat()来整理出最终导入的命令集合，以达到快速导入的目的。所以有一下几点需要注意的。

1. 因为RESP协议中的分隔符为在Linux下是\r\n，而在Windows下则为\n

2. 因为我的实际数据Description列中的数据行是为空的。所以根据参考，添加了特殊处理行： 'Description' AS hkey3, if(Description is not null, Description, ' ') AS hval3,

2. 第一行的 *18\r\n : *表示数组，8表示数组元素个数， \r\n是规定分隔符.我猜想的就是hkey+hval+redis_cmd+redis_key的数量总和(真的是猜的，因为我读不太懂)。

3.第二行的 '$',LENGTH(redis_cmd),'\r\n',redis_cmd,'\r\n', : $表示长字符串，LENGTH(redis_cmd)表示字符串长度，redis_cmd字符串变量，\r\n还是规定字符串

4.还有就是注意记事本的编码问题，如果出现MySQL ERROR 1300 (HY000): Invalid utf8 character string这个东西，那大概率就是字符编码的问题。记得点击另存为保存为utf-8编码。

5.ERR Protocol error: expected '$', got ''，这个问题就是第一个问题的错误提示

6.最后一个经验之谈，不建议直接复制粘贴，真的不建议，不建议。会出现各种莫名其妙的问题。跪了一下午。

4.执行脚本命令

mysql -h 数据库服务器地址 -u用户名 -p密码 -D数据库名 --default-character-set=utf8 --skip-column-names --raw < mysql-to-redis.sql | redis-cli.exe --pipe

PS：记得把中文改成对应的参数就可以了，至此就完成啦~

5.因为觉得每次手动替换字段字符串，遇到大表肯定会疯掉的。所以就自己撸了一个简单地方法，传入表名和空字段就可以生成文本的函数。没有经过严谨的测试，用的是TP框架。如果使用过程中遇到问题还请多多体谅。

public function makeRedisTxt($tableName,$nullColumn=array()){

$tableColumns = M($tableName)->getDbFields();

$count = count($tableColumns)*2+2;

$RedisTxt = "

SELECT CONCAT(

'*".$count."\\n',

'$',LENGTH(redis_cmd),'\\n',redis_cmd,'\\n',

'$',LENGTH(redis_key),'\\n',redis_key,'\\n',

";

foreach ($tableColumns as $key=> $column){

$RedisTxt.="'$',LENGTH(hkey".$key."),'\\n',hkey".$key.",'\\n','$',LENGTH(hval".$key."),'\\n',hval".$key.",'\\n',\n";

}

$RedisTxt = substr($RedisTxt,0,-7);//去除最后一行的， ,'\n',

$RedisTxt.= "

)FROM(

SELECT 'HMSET' AS redis_cmd,

concat_ws(':','xb_".$tableName."', ID) AS redis_key,

";

foreach ($tableColumns as $key=> $column){

$RedisTxt.="'".$column."' AS hkey".$key.", ".$column." AS hval".$key.",\n";

}

$RedisTxt = substr($RedisTxt,0,-2);

$RedisTxt .= "

From xb_".$tableName."

)AS t

";

//组装空字段数组

$newColumnArr = array();

if($nullColumn){

foreach ($nullColumn as $key => $column){

$newColumnArr[$column] = "if(".$column." is not null, ".$column." , '') ";

}

}

//替换空字段最后出现的位置为特殊处理行

if($newColumnArr){

foreach ($newColumnArr as $col => $colStr){

$start = strripos($RedisTxt,$col);//获取最后出现的位置，即开始替换的位置

$strlen = strlen($col);//获取所需替换字符长度

$RedisTxt = substr_replace($RedisTxt,$colStr,$start,$strlen);

}

}

return $RedisTxt;

/*****************生成结果如下**********************/

// SELECT CONCAT(

// '*18\n',

// '$', LENGTH(redis_cmd), '\n', redis_cmd, '\n',

// '$', LENGTH(redis_key), '\n', redis_key, '\n',

// '$', LENGTH(hkey0), '\n', hkey0, '\n', '$', LENGTH(hval0), '\n', hval0, '\n',

// '$', LENGTH(hkey1), '\n', hkey1, '\n', '$', LENGTH(hval1), '\n', hval1, '\n',

// '$', LENGTH(hkey2), '\n', hkey2, '\n', '$', LENGTH(hval2), '\n', hval2, '\n',

// '$', LENGTH(hkey3), '\n', hkey3, '\n', '$', LENGTH(hval3), '\n', hval3, '\n',

// '$', LENGTH(hkey4), '\n', hkey4, '\n', '$', LENGTH(hval4), '\n', hval4, '\n',

// '$', LENGTH(hkey5), '\n', hkey5, '\n', '$', LENGTH(hval5), '\n', hval5, '\n',

// '$', LENGTH(hkey6), '\n', hkey6, '\n', '$', LENGTH(hval6), '\n', hval6, '\n',

// '$', LENGTH(hkey7), '\n', hkey7, '\n', '$', LENGTH(hval7), '\n', hval7

// )FROM(

// SELECT 'HMSET' AS redis_cmd,

// concat_ws(':', 'xb_cp_name', ID) AS redis_key,

// 'ID' AS hkey0, ID AS hval0,

// 'Name' AS hkey1, Name AS hval1,

// 'Description' AS hkey2, if (Description is not null, Description , '') AS hval2,

// 'Status' AS hkey3, Status AS hval3,

// 'Sort' AS hkey4, Sort AS hval4,

// 'IsDel' AS hkey5, IsDel AS hval5,

// 'OperatorID' AS hkey6, OperatorID AS hval6,

// 'UpdateTime' AS hkey7, UpdateTime AS hval7

// From xb_cp_name

// )AS t

}

本作品采用《CC 协议》，转载必须注明作者和本文链接