Last active
January 1, 2016 09:59
-
-
Save kijtra/8129050 to your computer and use it in GitHub Desktop.
#MySQL #PHP 数値をBase53化するストアドファンクション(アルファベットとアンダースコアで53文字)。短縮URLなんかに良いかなと。ひとつの関数でエンコード&デコードどちらにも対応。ついでにPHP版の関数も。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
function base53($num) { | |
if(!is_numeric($num) && !is_string($num)){ | |
return false; | |
} | |
/* ここの文字列は並びをランダムにするといいと思います */ | |
$base='yfgs_RDVOTwztouMKxkPQIlvjprhmCNUdJcLYqaHBnAeFZSWXiEbG'; | |
$b=53; | |
if(is_numeric($num)){ | |
$r = $num % $b; | |
$re = $base{$r}; | |
$q = floor($num / $b); | |
while ($q) { | |
$r = $q % $b; | |
$q =floor($q/$b); | |
$re = $base{$r}.$re; | |
} | |
return $re; | |
} else if(preg_match('/^[a-zA-Z_]+$/',$num)) { | |
$l = strlen($num); | |
$re = 0; | |
$a = array_flip(str_split($base)); | |
for($i = 0; $i < $l; ++$i) { | |
vd(array($num{$i},$a[$num{$i}])); | |
$re += $a[$num{$i}] * pow($b, $l-$i-1); | |
} | |
return $re; | |
} | |
return false; | |
} | |
/* ex) | |
echo base53(259936169); => 'dibXf' | |
echo base53('dibXf'); => 259936169' | |
*/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE FUNCTION BASE53( _in VARCHAR(20)) RETURNS VARCHAR(20) | |
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA | |
BEGIN | |
DECLARE base CHAR(53); | |
DECLARE b TINYINT(53) UNSIGNED; | |
DECLARE r TINYINT(53) UNSIGNED; | |
DECLARE l INT UNSIGNED; | |
DECLARE q BIGINT UNSIGNED; | |
DECLARE re VARCHAR(255); | |
/* ここの文字列は並びをランダムにするといいと思います */ | |
SET base = 'yfgs_RDVOTwztouMKxkPQIlvjprhmCNUdJcLYqaHBnAeFZSWXiEbG'; | |
SET b = 53; | |
IF (_in REGEXP '^[0-9]+$') = 1 THEN | |
SET r = MOD(_in, b); | |
SET re = SUBSTRING(base, r+1, 1); | |
SET q = FLOOR(_in / b); | |
WHILE q > 0 DO | |
SET r = MOD(q, b); | |
SET q = FLOOR(q / b); | |
SET re = CONCAT(SUBSTRING(base, r+1, 1), re); | |
END WHILE; | |
ELSEIF (_in REGEXP '^[a-zA-Z_]+$') = 1 THEN | |
SET l = CHAR_LENGTH(_in); | |
SET re = 0; | |
SET q = 0; | |
WHILE q < l DO | |
SET re = re + ((LOCATE(CAST(SUBSTRING(_in, q+1, 1) AS BINARY), CAST(base AS BINARY)) - 1) * POW(b, l - q - 1)); | |
SET q = q + 1; | |
END WHILE; | |
RETURN re; | |
END IF; | |
RETURN re; | |
END; | |
/* ex) | |
SELECT BASE53(259936169); => 'dibXf' | |
SELECT BASE53('dibXf'); => '259936169' | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment