{"id":348,"date":"2019-07-05T14:45:09","date_gmt":"2019-07-05T05:45:09","guid":{"rendered":"https:\/\/took.jp\/?p=348"},"modified":"2020-10-21T12:31:44","modified_gmt":"2020-10-21T03:31:44","slug":"post-348","status":"publish","type":"post","link":"https:\/\/took.jp\/tech-blog\/post-348\/","title":{"rendered":"base64\u3067\u6697\u53f7\u5316\u3001\u5fa9\u53f7\u5316\u306e\u30b3\u30fc\u30c9\u3092\u66f8\u3044\u3066\u307f\u308b\u3000for MySQL"},"content":{"rendered":"<p>\u307e\u305a\u3001\u6697\u53f7\u5316\u3001\u5fa9\u53f7\u5316\u306e\u4ed5\u7d44\u307f\u3092\u81ea\u5206\u3067\u66f8\u3044\u3066\u307f\u3088\u3046\u3068\u601d\u3063\u305f\u7d4c\u7def\u3067\u3059\u304c\u3001\u3068\u3042\u308bDB\u3067MySql5.5\u3092\u4f7f\u7528\u3057\u3066\u3044\u307e\u3057\u3066\u3001<\/p>\n<p>\u958b\u767a\u4e2d\u306b\u3001base64\u306e\u6697\u53f7\u5316\u3092\u3059\u308bto_base64()\u3092sql\u3067\u4f7f\u7528\u3057\u305f\u304f\u306a\u3063\u305f\u306e\u3067\u3059\u304c\u3001<strong>\u52d5\u304b\u306a\u3044\u3002<\/strong><\/p>\n<p><img decoding=\"async\" class=\"alignnone wp-image-350\" src=\"https:\/\/took.jp\/wp-content\/uploads\/2019\/07\/b64-1-300x19.png\" alt=\"\" width=\"553\" height=\"35\" srcset=\"https:\/\/took.jp\/tech-blog\/wp-content\/uploads\/2019\/07\/b64-1-300x19.png 300w, https:\/\/took.jp\/tech-blog\/wp-content\/uploads\/2019\/07\/b64-1-768x49.png 768w, https:\/\/took.jp\/tech-blog\/wp-content\/uploads\/2019\/07\/b64-1.png 812w\" sizes=\"(max-width: 553px) 100vw, 553px\" \/><\/p>\n<p>\u305d\u308c\u3082\u305d\u306e\u306f\u305a\u3002<\/p>\n<p>\u3060\u3063\u3066\u3001\u305d\u306e\u95a2\u6570\u304c\u8ffd\u52a0\u3055\u308c\u305f\u306e\u306f\u3001MySql5.6\u304b\u3089\u306a\u306e\u3060\u304b\u3089\u3002<\/p>\n<p>\u30d0\u30fc\u30b8\u30e7\u30f3\u30a2\u30c3\u30d7\u3092\u3057\u3088\u3046\u304b\u8003\u3048\u307e\u3057\u305f\u304c\u3001\u521d\u3081\u3066\u306e\u4f5c\u696d\u3067\u3059\u3057\u3001\u306a\u306b\u3084\u3089\u5927\u5909\u305d\u3046\u3060\u3063\u305f\u306e\u3067\u3001\u65ad\u5ff5\u3002<\/p>\n<p>\u3067\u3082to_base64()\u3092\u4f7f\u3044\u305f\u3044\u3002<\/p>\n<p>\u305d\u3046\u3060\u3001\u4f5c\u308c\u3070\u3044\u3044\u3058\u3083\u306a\u3044\u304b\u3002<\/p>\n<p>\u305d\u3093\u306a\u3053\u3093\u306a\u3067\u307e\u305f\u7121\u99c4\u306a\u4f5c\u696d\u306b\u6ca1\u982d\u3057\u3066\u3057\u307e\u3044\u307e\u3057\u305f\u7b11<\/p>\n<p>&nbsp;<\/p>\n<p>base64\u3067\u306e\u6697\u53f7\u5316\u3001\u5fa9\u53f7\u5316\u3092\u4f5c\u6210\u3059\u308b\u306b\u3042\u305f\u3063\u3066\u3001\u3053\u3061\u3089\u3092\u53c2\u8003\u306b\u3057\u307e\u3057\u305f\u3002<\/p>\n<blockquote>\n<ol>\n<li>\u5143\u30c7\u30fc\u30bf\n<ul>\n<li>\u6587\u5b57\u5217: \"ABCDEFG\"<\/li>\n<li>16\u9032\u8868\u73fe: 41, 42, 43, 44, 45, 46, 47<\/li>\n<li>2\u9032\u8868\u73fe: 0100 0001, 0100 0010, 0100 0011, 0100 0100, 0100 0101, 0100 0110, 0100 0111<\/li>\n<\/ul>\n<\/li>\n<li>6\u30d3\u30c3\u30c8\u305a\u3064\u306b\u5206\u5272\n<ul>\n<li>010000 010100 001001 000011 010001 000100 010101 000110 010001 11<\/li>\n<\/ul>\n<\/li>\n<li>2\u30d3\u30c3\u30c8\u4f59\u308b\u306e\u3067\u30014\u30d3\u30c3\u30c8\u52060\u3092\u8ffd\u52a0\u3057\u30666\u30d3\u30c3\u30c8\u306b\u3059\u308b\n<ul>\n<li>010000 010100 001001 000011 010001 000100 010101 000110 010001 110000<\/li>\n<\/ul>\n<\/li>\n<li>\u5909\u63db\u8868\u306b\u3088\u308a\u30014\u6587\u5b57\u305a\u3064\u5909\u63db\n<ul>\n<li>\"QUJD\", \"REVG\", \"Rw\"<\/li>\n<\/ul>\n<\/li>\n<li>2\u6587\u5b57\u4f59\u308b\u306e\u3067\u30012\u6587\u5b57\u5206 = \u8a18\u53f7\u3092\u8ffd\u52a0\u3057\u30664\u6587\u5b57\u306b\u3059\u308b\n<ul>\n<li>\"QUJD\", \"REVG\", \"Rw==\"<\/li>\n<\/ul>\n<\/li>\n<li>Base64\u6587\u5b57\u5217\n<ul>\n<li>\"QUJDREVGRw==\"<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<p><img decoding=\"async\" class=\"alignnone wp-image-351\" src=\"https:\/\/took.jp\/wp-content\/uploads\/2019\/07\/table-300x221.png\" alt=\"\" width=\"497\" height=\"366\" srcset=\"https:\/\/took.jp\/tech-blog\/wp-content\/uploads\/2019\/07\/table-300x221.png 300w, https:\/\/took.jp\/tech-blog\/wp-content\/uploads\/2019\/07\/table-768x566.png 768w, https:\/\/took.jp\/tech-blog\/wp-content\/uploads\/2019\/07\/table.png 873w\" sizes=\"(max-width: 497px) 100vw, 497px\" \/><\/p><\/blockquote>\n<p>\uff08Wikipedia\u3088\u308a\u5f15\u7528\uff1ahttps:\/\/ja.wikipedia.org\/wiki\/Base64\uff09<\/p>\n<p>&nbsp;<\/p>\n<p>\u3053\u308c\u3060\u3051\u306e\u60c5\u5831\u304c\u3042\u308c\u3070\u3001\u3067\u304d\u305d\u3046\u3067\u3059\u306d\u3002<\/p>\n<p>\u3068\u3044\u3046\u3053\u3068\u3067\u3001\u66f8\u3044\u305f\u30b3\u30fc\u30c9\u304c\u3053\u3061\u3089\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<p>\u307e\u305a\u306f\u3001\u6697\u53f7\u5316\u3002<\/p>\n<pre class=\"line-numbers\" data-start=\"1\"><code class=\"language-sql\">delimiter \/\/\r\n \r\n CREATE FUNCTION to_base64(param varchar(10)) RETURNS varchar(100) DETERMINISTIC\r\n     BEGIN\r\n       DECLARE $SECOND varchar(100);\r\n       DECLARE $FONT_CODE varchar(100);\r\n       DECLARE $RESULT varchar(100);\r\n       DECLARE $RETURN_LIST varchar(100);\r\n       DECLARE $S_POSITION int;\r\n       DECLARE $DIFF_CODE int;\r\n       DECLARE $SPLIT_CHA varchar(30);\r\n       SET $RETURN_LIST = '';\r\n       \r\n       -- \u6587\u5b57\u5217\u309216\u9032\u6570\u8868\u73fe-&gt;2\u9032\u6570\u306b\u5909\u63db\u5f8c\u30012\u9032\u6570\u5909\u63db\u6642\u306bconv\u3067\u7701\u7565\u3055\u308c\u305f\u30010\u3092\u8ffd\u52a0\r\n       SELECT conv(hex(param),16,2) into $SECOND;\r\n       loop_add8: LOOP\r\n         IF CHAR_LENGTH($SECOND) % 8 = 0 THEN\r\n           LEAVE loop_add8;\r\n         END IF;\r\n         SELECT CONCAT(0,$SECOND) into $SECOND;\r\n         ITERATE loop_add8;\r\n       END LOOP loop_add8;\r\n    \r\n       -- 2\u9032\u6570\u30926\u6841\u3054\u3068\u306b\u533a\u5207\u308a\u30016\u6841\u306b\u6e80\u305f\u306a\u3044\u90e8\u5206\u306f0\u3067\u88dc\u3046\r\n       loop_split6: LOOP\r\n         IF CHAR_LENGTH($SECOND) % 6 = 0 THEN\r\n           LEAVE loop_split6;\r\n         END IF;\r\n         SELECT concat($SECOND,0) into $SECOND;\r\n         ITERATE loop_split6;\r\n       END LOOP loop_split6;\r\n       \r\n       -- \u5909\u63db\u8868\u306b\u3088\u308a\u5909\u63db\r\n       SET $S_POSITION = 1;\r\n       loop_conversion: LOOP\r\n         SELECT substring($SECOND, $S_POSITION , 6) into $SPLIT_CHA;\r\n         SELECT conv($SPLIT_CHA,2,10) into $FONT_CODE;\r\n         \r\n         SET $DIFF_CODE =(\r\n         CASE WHEN $FONT_CODE &lt;= 25 THEN  65\r\n\t          WHEN $FONT_CODE &lt;= 51 THEN  61\r\n\t          WHEN $FONT_CODE &lt;= 61 THEN  -4\r\n\t          WHEN $FONT_CODE  = 62 THEN -19\r\n\t          WHEN $FONT_CODE  = 63 THEN -16\r\n\t     END);\r\n\r\n         SELECT unhex(conv(conv($SPLIT_CHA,2,10)+$DIFF_CODE,10,16)) into $RESULT;\r\n         SELECT concat($RETURN_LIST,$RESULT) into $RETURN_LIST;\r\n         SET $S_POSITION  = $S_POSITION + 6;\r\n    \t IF  $S_POSITION &gt;= char_length($SECOND) THEN \r\n    \t   LEAVE loop_conversion;\r\n         END IF;\r\n         ITERATE loop_conversion;\r\n       END LOOP  loop_conversion;\r\n\t   \r\n\t   -- 4\u6587\u5b57\u3054\u3068\u306b\u533a\u5207\u308a\u30014\u6587\u5b57\u306b\u898b\u305f\u3044\u306a\u3044\u90e8\u5206\u306b=\u3092\u8ffd\u52a0\u3059\u308b\r\n       loop_add_equal: LOOP\r\n         IF char_length($RETURN_LIST) % 4 = 0 THEN\r\n           LEAVE loop_add_equal;\r\n         END IF;\r\n         SELECT concat($RETURN_LIST,'=') into $RETURN_LIST;\r\n         ITERATE loop_add_equal;\r\n       END LOOP loop_add_equal;\r\n       RETURN $RETURN_LIST;\r\n     END;\r\n     \r\n\/\/<\/code><\/pre>\n<p>&nbsp;<\/p>\n<p>\u305d\u3057\u3066\u3001\u5fa9\u53f7\u5316\u306e\u30b3\u30fc\u30c9\u304c\u3053\u3061\u3089\u3067\u3059\u3002<\/p>\n<p>\u6697\u53f7\u5316\u306e\u307b\u3068\u3093\u3069\u9006\u306e\u624b\u9806\u3092\u8e0f\u3081\u3070\u826f\u3044\u305f\u3081\u3001\u30b3\u30e1\u30f3\u30c8\u306f\u3042\u307e\u308a\u8a18\u8f09\u3057\u3066\u3044\u307e\u305b\u3093\u3002<\/p>\n<pre class=\"line-numbers\" data-start=\"1\"><code class=\"language-sql\">delimiter \/\/\r\n \r\n CREATE FUNCTION from_base64(param varchar(100)) RETURNS varchar(100) DETERMINISTIC\r\n \r\n     BEGIN\r\n       DECLARE $EQUAl_TRIM varchar(100);\r\n       DECLARE $FONT_CODE varchar(100);\r\n       DECLARE $RESULT varchar(100);\r\n       DECLARE $RETURN_LIST varchar(100);\r\n       DECLARE $TMP_LIST varchar(100);\r\n       DECLARE $CUR int;\r\n       DECLARE $CUR_SPL int;\r\n       DECLARE $DIFF_CODE int;\r\n       DECLARE $SPLIT_CHA varchar(30);\r\n       SET $RETURN_LIST = '';\r\n       SET $TMP_LIST = '';\r\n       SET $EQUAl_TRIM = TRIM( '=' FROM param );\r\n       SET $FONT_CODE = conv(hex($EQUAL_TRIM),16,10);\r\n\r\n       SET $CUR = 1;\r\n       SET $CUR_SPL = 1;\r\n       loop_conversion: LOOP\r\n         SELECT substring($EQUAl_TRIM, $CUR , 1) into $SPLIT_CHA;\r\n         \r\n         SELECT conv(hex($SPLIT_CHA),16,10) into $FONT_CODE;\r\n         \r\n         SET $DIFF_CODE =(\r\n         CASE WHEN $FONT_CODE &lt;=  57 THEN   4\r\n         \t  WHEN $FONT_CODE &lt;=  90 THEN -65\r\n         \t  WHEN $FONT_CODE &lt;= 122 THEN -71\r\n              WHEN $FONT_CODE =   47 THEN  16\r\n\t          WHEN $FONT_CODE =   43 THEN  19\r\n\t     END);\r\n\t     \r\n         SELECT conv($FONT_CODE + $DIFF_CODE,10,2) into $RESULT;\r\n         \r\n       loop_add6: LOOP\r\n         IF CHAR_LENGTH($RESULT) % 6 = 0 THEN\r\n           LEAVE loop_add6;\r\n         END IF;\r\n         SELECT CONCAT(0,$RESULT) into $RESULT;\r\n         ITERATE loop_add6;\r\n       END LOOP loop_add6;\r\n       \r\n         SELECT concat($TMP_LIST,$RESULT) into $TMP_LIST;\r\n\r\n         SET $CUR  = $CUR + 1;\r\n    \t IF  $CUR &gt; char_length($EQUAl_TRIM) THEN \r\n    \t   LEAVE loop_conversion;\r\n         END IF;\r\n         \r\n         ITERATE loop_conversion;\r\n       END LOOP  loop_conversion;\r\n\r\n       loop_split8: LOOP\r\n         SELECT substring($TMP_LIST, $CUR_SPL , 8) into $SPLIT_CHA;\r\n         \r\n         SELECT concat($RETURN_LIST,unhex(conv($SPLIT_CHA,2,16))) into $RETURN_LIST;\r\n         \r\n         SET $CUR_SPL  = $CUR_SPL + 8;\r\n         \r\n    \t IF  $CUR_SPL &gt;= char_length($TMP_LIST) THEN \r\n    \t   LEAVE loop_split8;\r\n         END IF;\r\n                \r\n         ITERATE loop_split8;\r\n       END LOOP  loop_split8;\r\n       SELECT TRIM( $RETURN_LIST ) into $RETURN_LIST;\r\n       RETURN $RETURN_LIST;\r\n       \r\n     END;\r\n     \r\n\/\/<\/code><\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>MySql\u306e\u30d0\u30fc\u30b8\u30e7\u30f3\u30a2\u30c3\u30d7\u304c\u3081\u3093\u3069\u304f\u3055\u3044\u3068\u3044\u3046\u65b9\u306f\u4f7f\u3063\u3066\u307f\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u307e\u305a\u3001\u6697\u53f7\u5316\u3001\u5fa9\u53f7\u5316\u306e\u4ed5\u7d44\u307f\u3092\u81ea\u5206\u3067\u66f8\u3044\u3066\u307f\u3088\u3046\u3068\u601d\u3063\u305f\u7d4c\u7def\u3067\u3059\u304c\u3001\u3068\u3042\u308bDB\u3067MySql5.5\u3092\u4f7f\u7528\u3057\u3066\u3044\u307e\u3057\u3066\u3001 \u958b\u767a\u4e2d\u306b\u3001base64\u306e\u6697\u53f7\u5316\u3092\u3059\u308bto_base64()\u3092sql\u3067\u4f7f\u7528\u3057\u305f\u304f\u306a\u3063\u305f\u306e\u3067\u3059\u304c\u3001\u52d5\u304b\u306a\u3044\u3002 \u305d\u308c\u3082\u305d\u306e\u306f\u305a\u3002\u2026<\/p>\n","protected":false},"author":3,"featured_media":1988,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62,71],"tags":[90],"class_list":["post-348","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-it","category-mysql","tag-pickup"],"_links":{"self":[{"href":"https:\/\/took.jp\/tech-blog\/wp-json\/wp\/v2\/posts\/348","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/took.jp\/tech-blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/took.jp\/tech-blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/took.jp\/tech-blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/took.jp\/tech-blog\/wp-json\/wp\/v2\/comments?post=348"}],"version-history":[{"count":0,"href":"https:\/\/took.jp\/tech-blog\/wp-json\/wp\/v2\/posts\/348\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/took.jp\/tech-blog\/wp-json\/wp\/v2\/media\/1988"}],"wp:attachment":[{"href":"https:\/\/took.jp\/tech-blog\/wp-json\/wp\/v2\/media?parent=348"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/took.jp\/tech-blog\/wp-json\/wp\/v2\/categories?post=348"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/took.jp\/tech-blog\/wp-json\/wp\/v2\/tags?post=348"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}