当前位置：K88软件开发 → 文章中心 → 编程语言 → SQL → SQL01 → 文章内容

在sql语句中实现md5功能

减小字体

增大字体作者：wangsdong 来源：asp编程网发布时间：2018-12-30 9:13:35

最近在做主站会员与discuz论坛会员整合的时候，遇到一个这样的问题
主站会员密码没有加密，而论坛会员要求加密。现在要将主站的会员导入到论坛中去，所以要给它加密。但是现在的问题是我的会员量太大，如果写个程序去导入，时间会很长，但程序又会出超时，如果直接在sql server中的有md5功能就好办了。直接在数据库中运行一下就行了。
在网上找了一段时间，找到了这个函数，终于实现了这个功能。
代码如下：

IF
EXISTS(SELECT
*
FROM
dbo.sysobjects
WHERE
id
=
OBJECT_ID(N'[dbo].[MD5_m_OnBits]')
AND
xtype
IN(N'FN',
N'IF',
N'TF'))
DROP
FUNCTION
[dbo].[MD5_m_OnBits]
GO
/*****************************************************************************
*
Name:
MD5_m_OnBits
*
Description:
常数组
*****************************************************************************/
CREATE
FUNCTION
dbo.MD5_m_OnBits(
@i
TINYINT
)
RETURNS
INT
WITH
ENCRYPTION
AS
BEGIN
DECLARE
@iRes
INT
SELECT
@iRes
=
CASE
@i
WHEN
0
THEN
1
--
00000000000000000000000000000001
WHEN
1
THEN
3
--
00000000000000000000000000000011
WHEN
2
THEN
7
--
00000000000000000000000000000111
WHEN
3
THEN
15
--
00000000000000000000000000001111
WHEN
4
THEN
31
--
00000000000000000000000000011111
WHEN
5
THEN
63
--
00000000000000000000000000111111
WHEN
6
THEN
127
--
00000000000000000000000001111111
WHEN
7
THEN
255
--
00000000000000000000000011111111
WHEN
8
THEN
511
--
00000000000000000000000111111111
WHEN
9
THEN
1023
--
00000000000000000000001111111111
WHEN
10
THEN
2047
--
00000000000000000000011111111111
WHEN
11
THEN
4095
--
00000000000000000000111111111111
WHEN
12
THEN
8191
--
00000000000000000001111111111111
WHEN
13
THEN
16383
--
00000000000000000011111111111111
WHEN
14
THEN
32767
--
00000000000000000111111111111111
WHEN
15
THEN
65535
--
00000000000000001111111111111111
WHEN
16
THEN
131071
--
00000000000000011111111111111111
WHEN
17
THEN
262143
--
00000000000000111111111111111111
WHEN
18
THEN
524287
--
00000000000001111111111111111111
WHEN
19
THEN
1048575
--
00000000000011111111111111111111
WHEN
20
THEN
2097151
--
00000000000111111111111111111111
WHEN
21
THEN
4194303
--
00000000001111111111111111111111
WHEN
22
THEN
8388607
--
00000000011111111111111111111111
WHEN
23
THEN
16777215
--
00000000111111111111111111111111
WHEN
24
THEN
33554431
--
00000001111111111111111111111111
WHEN
25
THEN
67108863
--
00000011111111111111111111111111
WHEN
26
THEN
134217727
--
00000111111111111111111111111111
WHEN
27
THEN
268435455
--
00001111111111111111111111111111
WHEN
28
THEN
536870911
--
00011111111111111111111111111111
WHEN
29
THEN
1073741823
--
00111111111111111111111111111111
WHEN
30
THEN
2147483647
--
01111111111111111111111111111111
ELSE
0
END
RETURN(@iRes)
END
GO
IF
EXISTS(SELECT
*
FROM
dbo.sysobjects
WHERE
id
=
OBJECT_ID(N'[dbo].[MD5_m_2Power]')
AND
xtype
IN(N'FN',
N'IF',
N'TF'))
DROP
FUNCTION
[dbo].[MD5_m_2Power]
GO
/*****************************************************************************
*
Name:
MD5_m_2Power
*
Description:
常数组
*****************************************************************************/
CREATE
FUNCTION
dbo.MD5_m_2Power(
@i
TINYINT
)
RETURNS
INT
WITH
ENCRYPTION
AS
BEGIN
DECLARE
@iRes
INT
SELECT
@iRes
=
CASE
@i
WHEN
0
THEN
1
--
00000000000000000000000000000001
WHEN
1
THEN
2
--
00000000000000000000000000000010
WHEN
2
THEN
4
--
00000000000000000000000000000100
WHEN
3
THEN
8
--
00000000000000000000000000001000
WHEN
4
THEN
16
--
00000000000000000000000000010000
WHEN
5
THEN
32
--
00000000000000000000000000100000
WHEN
6
THEN
64
--
00000000000000000000000001000000
WHEN
7
THEN
128
--
00000000000000000000000010000000
WHEN
8
THEN
256
--
00000000000000000000000100000000
WHEN
9
THEN
512
--
00000000000000000000001000000000
WHEN
10
THEN
1024
--
00000000000000000000010000000000
WHEN
11
THEN
2048
--
00000000000000000000100000000000
WHEN
12
THEN
4096
--
00000000000000000001000000000000
WHEN
13
THEN
8192
--
00000000000000000010000000000000
WHEN
14
THEN
16384
--
00000000000000000100000000000000
WHEN
15
THEN
32768
--
00000000000000001000000000000000
WHEN
16
THEN
65536
--
00000000000000010000000000000000
WHEN
17
THEN
131072
--
00000000000000100000000000000000
WHEN
18
THEN
262144
--
00000000000001000000000000000000
WHEN
19
THEN
524288
--
00000000000010000000000000000000
WHEN
20
THEN
1048576
--
00000000000100000000000000000000
WHEN
21
THEN
2097152
--
00000000001000000000000000000000
WHEN
22
THEN
4194304
--
00000000010000000000000000000000
WHEN
23
THEN
8388608
--
00000000100000000000000000000000
WHEN
24
THEN
16777216
--
00000001000000000000000000000000
WHEN
25
THEN
33554432
--
00000010000000000000000000000000
WHEN
26
THEN
67108864
--
00000100000000000000000000000000
WHEN
27
THEN
134217728
--
00001000000000000000000000000000
WHEN
28
THEN
268435456
--
00010000000000000000000000000000
WHEN
29
THEN
536870912
--
00100000000000000000000000000000
WHEN
30
THEN
1073741824
--
01000000000000000000000000000000
ELSE
0
END
RETURN(@iRes)
END
GO
IF
EXISTS(SELECT
*
FROM
dbo.sysobjects
WHERE
id
=
OBJECT_ID(N'[dbo].[MD5_LShift]')
AND
xtype
IN(N'FN',
N'IF',
N'TF'))
DROP
FUNCTION
[dbo].[MD5_LShift]
GO
/*****************************************************************************
*
Name:
MD5_LShift
*
Description:
MD5_LShift
*****************************************************************************/
CREATE
FUNCTION
dbo.MD5_LShift(
@ivalue
INT
,@iShiftBits
TINYINT
)
RETURNS
INT
WITH
ENCRYPTION
AS
BEGIN
DECLARE
@iRes
BIGINT
SET
@iRes
=
CAST(@ivalue
AS
BINARY(8))
SET
@iRes
=
@iRes
*
dbo.MD5_m_2Power(@iShiftBits)
RETURN(CAST(@iRes
&

0x00000000FFFFFFFF
AS
BINARY(4)))
END
GO
IF
EXISTS(SELECT
*
FROM
dbo.sysobjects
WHERE
id
=
OBJECT_ID(N'[dbo].[MD5_RShift]')
AND
xtype
IN(N'FN',
N'IF',
N'TF'))
DROP
FUNCTION
[dbo].[MD5_RShift]
GO
/*****************************************************************************
*
Name:
MD5_RShift
*
Description:
MD5_RShift
*****************************************************************************/
CREATE
FUNCTION
dbo.MD5_RShift(
@ivalue
INT
,@iShiftBits
TINYINT
)
RETURNS
INT
WITH
ENCRYPTION
AS
BEGIN
DECLARE
@iRes
BIGINT
SET
@iRes
=
CAST(@ivalue
AS
BINARY(8))
SET
@iRes
=
@iRes
/
dbo.MD5_m_2Power(@iShiftBits)
RETURN(CAST(@iRes
&

0x00000000FFFFFFFF
AS
BINARY(4)))
END
GO
IF
EXISTS(SELECT
*
FROM
dbo.sysobjects
WHERE
id
=
OBJECT_ID(N'[dbo].[MD5_RotateLeft]')
AND
xtype
IN(N'FN',
N'IF',
N'TF'))
DROP
FUNCTION
[dbo].[MD5_RotateLeft]
GO
/*****************************************************************************
*
Name:
MD5_RotateLeft
*
Description:
MD5_RotateLeft
*****************************************************************************/
CREATE
FUNCTION
dbo.MD5_RotateLeft(
@ivalue
INT
,@iShiftBits
TINYINT
)
RETURNS
INT
WITH
ENCRYPTION
AS
BEGIN
RETURN(dbo.MD5_LShift(@ivalue,
@iShiftBits)
|
dbo.MD5_RShift(@ivalue,
(32
-
@iShiftBits)))
END
GO
IF
EXISTS(SELECT
*
FROM
dbo.sysobjects
WHERE
id
=
OBJECT_ID(N'[dbo].[MD5_AddUnsigned]')
AND
xtype
IN(N'FN',
N'IF',
N'TF'))
DROP
FUNCTION
[dbo].[MD5_AddUnsigned]
GO
/*****************************************************************************
*
Name:
MD5_AddUnsigned
*
Description:
MD5_AddUnsigned
*****************************************************************************/
CREATE
FUNCTION
dbo.MD5_AddUnsigned(
@iX
INT
,@iY
INT
)
RETURNS
INT
WITH
ENCRYPTION
AS
BEGIN
DECLARE
@iRes
BIGINT
SET
@iRes
=
CAST(CAST(@iX
AS
BINARY(8))
AS
BIGINT)
+
CAST(CAST(@iY
AS
BINARY(8))
AS
BIGINT)
RETURN(CAST(@iRes
&

0x00000000FFFFFFFF
AS
BINARY(4)))
END
GO
IF
EXISTS(SELECT
*
FROM
dbo.sysobjects
WHERE
id
=
OBJECT_ID(N'[dbo].[MD5_F]')
AND
xtype
IN(N'FN',
N'IF',
N'TF'))
DROP
FUNCTION
[dbo].[MD5_F]
GO
/*****************************************************************************
*
Name:
MD5_F
*
Description:
MD5_F
*****************************************************************************/
CREATE
FUNCTION
dbo.MD5_F(
@x
INT
,@y
INT
,@z
INT
)
RETURNS
INT
WITH
ENCRYPTION
AS
BEGIN
RETURN((@x
&

@y)
|
((~@x)
&

@z))
END
GO
IF
EXISTS(SELECT
*
FROM
dbo.sysobjects
WHERE
id
=
OBJECT_ID(N'[dbo].[MD5_G]')
AND
xtype
IN(N'FN',
N'IF',
N'TF'))
DROP
FUNCTION
[dbo].[MD5_G]
GO
/*****************************************************************************
*
Name:
MD5_G
*
Description:
MD5_G
*****************************************************************************/
CREATE
FUNCTION
dbo.MD5_G(
@x
INT
,@y
INT
,@z
INT
)
RETURNS
INT
WITH
ENCRYPTION
AS
BEGIN
RETURN((@x
&

@z)
|
(@y
&

(~@z)))
END
GO
IF
EXISTS(SELECT
*
FROM
dbo.sysobjects
WHERE
id
=
OBJECT_ID(N'[dbo].[MD5_H]')
AND
xtype
IN(N'FN',
N'IF',
N'TF'))
DROP
FUNCTION
[dbo].[MD5_H]
GO
/*****************************************************************************
*
Name:
MD5_H
*
Description:
MD5_H
*****************************************************************************/
CREATE
FUNCTION
dbo.MD5_H(
@x
INT
,@y
INT
,@z
INT
)
RETURNS
INT
WITH
ENCRYPTION
AS
BEGIN
RETURN(@x
^
@y
^
@z)
END
GO
IF
EXISTS(SELECT
*
FROM
dbo.sysobjects
WHERE
id
=
OBJECT_ID(N'[dbo].[MD5_I]')
AND
xtype
IN(N'FN',
N'IF',
N'TF'))
DROP
FUNCTION
[dbo].[MD5_I]
GO
/*****************************************************************************
*
Name:
MD5_I
*
Description:
MD5_I
*****************************************************************************/
CREATE
FUNCTION
dbo.MD5_I(
@x
INT
,@y
INT
,@z
INT
)
RETURNS
INT
WITH
ENCRYPTION
AS
BEGIN

在sql语句中实现md5功能

[] [返回上一页] [打印]

·上一篇文章：查询用户发布的最新两条新闻的思路
·下一篇文章：SQL语言中去掉小数点有效数字后面的所有0

在sql语句中实现md5功能

文章评论评论内容只代表网友观点，与本站立场无关！

频道栏目导航

本类热门阅览

相关文章