When trying to optimize some queries to not depend on PHP to do something that could come ready from the database, I was trying to adapt the two routines below into functions > from MySQL:
function encode( $input, $index ) {
$base = strlen( $index );
$input += pow( $base, 4 );
$output = '';
for( $i = floor( log( $input, $base ) ); $i >= 0; $i-- ) {
$bcp = bcpow( $base, $i );
$start = floor( $input / $bcp ) % $base;
$output .= substr( $index, $start, 1 );
$input = $input - ( $start * $bcp );
}
return $output;
}
E:
function decode( $input, $index ) {
$base = strlen( $index );
$output = 0;
$length = strlen( $input ) - 1;
for( $i = 0; $i <= $length; $i++ ) {
$bcpow = bcpow( $base, $length - $i );
$output += strpos( $index, substr( $input, $i, 1 ) ) * $bcpow;
}
$output -= pow( $base, 4 );
$output = sprintf( '%F', $output );
$output = substr( $output, 0, strpos( $output, '.' ) );
return (int) $output;
}
They are functions that encode an integer into a string and vice versa, an AlphaID , almost like YouTube. >
As I have never created a MySQL function , I have read the manual (which is not exactly user friendly) and I have arrived, at least as far as function from coding, in this:
DROP FUNCTION IF EXISTS ENCODE_ALPHAID;
DELIMITER $$
CREATE FUNCTION ENCODE_ALPHAID( input integer ) RETURNS CHAR( 6 ) DETERMINISTIC
BEGIN
DECLARE output CHAR( 6 );
DECLARE letters CHAR( 62 );
DECLARE base TINYINT( 2 );
DECLARE iterator TINYINT( 2 );
DECLARE bcp CHAR( 9 );
DECLARE start TINYINT( 2 );
SET output = '';
SET letters = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
SET base = CHAR_LENGTH( letters );
SET input = input + POW( base, 4 );
SET iterator = FLOOR( LOG( base, input ) );
ENCODING: LOOP
SET bcp = POW( base, iterator );
SET start = FLOOR( input / bcp ) % base + 1;
SET output = CONCAT( output, SUBSTR( letters, start, 1 ) );
SET input = input - ( start * bcp );
SET iterator = iterator-1;
IF iterator < 0 THEN LEAVE ENCODING; END IF;
END LOOP ENCODING;
RETURN output;
END $$
DELIMITER ;
The routine was created successfully but has some problems:
As for the decoding function:
DROP FUNCTION IF EXISTS DECODE_ALPHAID;
DELIMITER $$
CREATE FUNCTION DECODE_ALPHAID( input integer ) RETURNS INTEGER( 10 ) DETERMINISTIC
BEGIN
DECLARE output INT( 10 );
DECLARE letters CHAR( 62 );
DECLARE base TINYINT( 1 );
DECLARE length TINYINT( 1 );
DECLARE iterator TINYINT( 1 );
DECLARE bcp CHAR( 9 );
DECLARE dot CHAR( 1 );
SET output = 0;
SET letters = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
SET base = CHAR_LENGTH( letters );
SET length = CHAR_LENGTH( input ) - 1;
SET iterator = 0;
SET dot = '.';
ENCODING: LOOP
SET bcp = POW( base, length - iterator );
SET output = output + LOCATE( SUBSTR( input, iterator, 1 ), letters ) * bcp;
SET iterator = iterator+1;
IF iterator <= length THEN LEAVE ENCODING; END IF;
END LOOP ENCODING;
SET output = ( output - POW( base, 4 ) ) + 0.0;
SET output = SUBSTR( output, 0, LOCATE( dot, output ) );
RETURN output;
END $$
DELIMITER ;
It even decodes and, I believe, by the way original author of the functions in PHP operated the results, formatting the mathematical calculation for float , operating as string and converting back to integer that I lacked knowledge in adapt correctly.