mysql encode&decode

MySQL urlencode 函数、urldecode函数 、multiurldecode 函数

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

DELIMITER ;

 

DROPFUNCTION IF EXISTS urlencode;

 

DELIMITER |

 

CREATEFUNCTION urlencode (s VARCHAR(4096))RETURNSVARCHAR(4096)

DETERMINISTIC

CONTAINSSQL

BEGIN

       DECLAREc VARCHAR(4096)DEFAULT'';

       DECLAREpointer INTDEFAULT 1;

       DECLAREs2 VARCHAR(4096)DEFAULT'';

 

       IFISNULL(s)THEN

           RETURNNULL;

       ELSE

       SETs2 = '';

       WHILE pointer <= length(s) DO

          SETc = MID(s,pointer,1);

          IF c = ' ' THEN

             SETc = '+';

          ELSEIFNOT(ASCII(c) BETWEEN48 AND57 OR

                ASCII(c)BETWEEN65 AND90 OR

                ASCII(c)BETWEEN97 AND122) THEN

             SETc = concat("%",LPAD(CONV(ASCII(c),10,16),2,0));

          ENDIF;

          SETs2 = CONCAT(s2,c);

          SETpointer = pointer + 1;

       ENDwhile;

       ENDIF;

       RETURNs2;

END;

|

DELIMITER ;

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

DROPFUNCTION IF EXISTS urldecode;

 

DELIMITER |

 

CREATEFUNCTION urldecode (s VARCHAR(4096))RETURNSVARCHAR(4096)

DETERMINISTIC

CONTAINSSQL

BEGIN

       DECLAREc VARCHAR(4096)DEFAULT'';

       DECLAREpointer INTDEFAULT 1;

       DECLAREh CHAR(2);

       DECLAREh1 CHAR(1);

       DECLAREh2 CHAR(1);

       DECLAREs2 VARCHAR(4096)DEFAULT'';

 

       IFISNULL(s)THEN

          RETURNNULL;

       ELSE

       SETs2 = '';

       WHILE pointer <= LENGTH(s) DO

          SETc = MID(s,pointer,1);

          IF c = '+'THEN

             SETc = ' ';

          ELSEIF c = '%'AND pointer + 2 <= LENGTH(s) THEN

             SETh1 = LOWER(MID(s,pointer+1,1));

             SETh2 = LOWER(MID(s,pointer+2,1));

             IF (h1 BETWEEN'0' AND '9' OR h1 BETWEEN'a' AND 'f')

                 AND

                 (h2BETWEEN'0' AND '9' OR h2 BETWEEN'a' AND 'f')

                 THEN

                   SETh = CONCAT(h1,h2);

                   SETpointer = pointer + 2;

                   SETc = CHAR(CONV(h,16,10));

              ENDIF;

          ENDIF;

          SETs2 = CONCAT(s2,c);

          SETpointer = pointer + 1;

       ENDwhile;

       ENDIF;

       RETURNs2;

END;

  

|

 

DELIMITER ;

当一个字符串经过多次 urlencode 处理之后，可以使用 multiurldecode来解码，它将多次调用 urldecode来解码，直到没有需要解码的字符

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

DELIMITER ;

 

DROPFUNCTION IF EXISTS multiurldecode;

 

DELIMITER |

 

CREATEFUNCTION multiurldecode (s VARCHAR(4096))RETURNSVARCHAR(4096)

DETERMINISTIC

CONTAINSSQL

BEGIN

       DECLAREpr VARCHAR(4096)DEFAULT'';

       IFISNULL(s)THEN

          RETURNNULL;

       ENDIF;      

       REPEAT

          SETpr = s;

          SELECTurldecode(s) INTOs;

       UNTIL pr = s ENDREPEAT;

       RETURNs;

END;

  

|

 

DELIMITER ;