分类目录归档：MySQL

多种方式的md5加密

发表于 2010 年 8 月 1 日 由 惜分飞

一、oracle实现md5加密：

CREATE OR REPLACE function fn_md5(input_string VARCHAR2) return varchar2
IS
raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
decrypted_raw RAW(2048);
error_in_input_buffer_length EXCEPTION;
BEGIN
sys.dbms_obfuscation_toolkit.MD5(input => raw_input,checksum => decrypted_raw);
return rawtohex(decrypted_raw);
END;

二、mysql实现md5加密：

select upper(md5('abc')) from cdb_settings limit 1;


注意问题：在mysql中不知道为什么使用了upper不能转化为大写，尝试了几个编码的mysql库都不行，也许是我mysql版本的问题，先记录下mysql版本

三、Sql Server 实现md5加密：

select hashbytes('MD5', 'abc');

四、C#实现md5加密：

string abc=System.Web.Security.FormsAuthentication.
HashPasswordForStoringInConfigFile("abc", "MD5") ;
 Response.Write(abc);

五、Javascript实现md5加密：

var hexcase = 0; function hex_md5(a)
{ return rstr2hex(rstr_md5(str2rstr_utf8(a))) }
function hex_hmac_md5(a, b)
{ return rstr2hex(rstr_hmac_md5(str2rstr_utf8(a), str2rstr_utf8(b))) }
 function md5_vm_test()
{ return hex_md5("abc").toLowerCase() == "900150983cd24fb0d6963f7d28e17f72" }
 function rstr_md5(a)
{ return binl2rstr(binl_md5(rstr2binl(a), a.length * 8)) }
 function rstr_hmac_md5(c, f)
{ var e = rstr2binl(c); if (e.length > 16)
{ e = binl_md5(e, c.length*8) }
 var a = Array(16), d = Array(16);
 for (var b = 0; b < 16; b++)
{ a[b] = e[b] ^ 909522486; d[b] = e[b] ^ 1549556828 }
var g = binl_md5(a.concat(rstr2binl(f)), 512 + f.length * 8);
return binl2rstr(binl_md5(d.concat(g), 512 + 128)) }
function rstr2hex(c) { try { hexcase } catch (g) { hexcase = 0 }
 var f = hexcase ? "0123456789ABCDEF" : "0123456789abcdef";
 var b = ""; var a; for (var d = 0; d < c.length; d++) { a = c.charCodeAt(d);
 b += f.charAt((a >>> 4) & 15) + f.charAt(a & 15) } return b } function str2rstr_utf8(c)
 { var b = ""; var d = -1; var a, e; while (++d < c.length)
{ a = c.charCodeAt(d); e = d + 1 < c.length ? c.charCodeAt(d + 1) : 0;
 if (55296 <= a && a <= 56319 && 56320 <= e && e <= 57343)
{ a = 65536 + ((a & 1023) << 10) + (e & 1023); d++ }
if (a <= 127) { b += String.fromCharCode(a) }
else { if (a <= 2047) { b += String.fromCharCode(192 | ((a >>> 6) & 31),
 128 | (a & 63)) } else { if (a <= 65535) { b += String.fromCharCode(224
| ((a >>> 12) & 15), 128 | ((a >>> 6) & 63), 128 | (a & 63)) }
else { if (a <= 2097151) { b += String.fromCharCode(240 |
((a >>> 18) & 7), 128 | ((a >>> 12) & 63), 128 | ((a >>> 6) & 63),
 128 | (a & 63)) } } } } } return b } function rstr2binl(b)
{ var a = Array(b.length >> 2); for (var c = 0; c<a.length; c++)
 { a1 = 0 } for (var c = 0; c <b.length*8;c =c+8 )
{ a1 |= (b.charCodeAt(c / 8 ) & 255) << (c % 32) } return a }
function binl2rstr(b) { var a = ""; for (var c = 0; c<b.length * 32; c=c+8)
 { a += String.fromCharCode((b1 >>> (c % 32)) & 255) }
 return a } function binl_md5(p, k) { p[k >> 5] |= 128 << ((k) % 32);
p[(((k + 64) >>> 9) << 4) + 14] = k; var o = 1732584193;
var n = -271733879; var m = -1732584194; var l = 271733878;
 for (var g = 0; g < p.length; g += 16) { var j = o; var h = n;
var f = m; var e = l; o = md5_ff(o, n, m, l, p[g + 0], 7, -680876936);
 l = md5_ff(l, o, n, m, p[g + 1], 12, -389564586); m = md5_ff(m, l, o, n, p[g + 2], 17, 606105819);
n = md5_ff(n, m, l, o, p[g + 3], 22, -1044525330); o = md5_ff(o, n, m, l, p[g + 4], 7, -176418897);
l = md5_ff(l, o, n, m, p[g + 5], 12, 1200080426); m = md5_ff(m, l, o, n, p[g + 6], 17, -1473231341);
 n = md5_ff(n, m, l, o, p[g + 7], 22, -45705983); o = md5_ff(o, n, m, l, p[g + 8], 7, 1770035416);
l = md5_ff(l, o, n, m, p[g + 9], 12, -1958414417); m = md5_ff(m, l, o, n, p[g + 10], 17, -42063);
 n = md5_ff(n, m, l, o, p[g + 11], 22, -1990404162); o = md5_ff(o, n, m, l, p[g + 12], 7, 1804603682);
 l = md5_ff(l, o, n, m, p[g + 13], 12, -40341101); m = md5_ff(m, l, o, n, p[g + 14], 17, -1502002290);
n = md5_ff(n, m, l, o, p[g + 15], 22, 1236535329); o = md5_gg(o, n, m, l, p[g + 1], 5, -165796510);
 l = md5_gg(l, o, n, m, p[g + 6], 9, -1069501632); m = md5_gg(m, l, o, n, p[g + 11], 14, 643717713);
n = md5_gg(n, m, l, o, p[g + 0], 20, -373897302); o = md5_gg(o, n, m, l, p[g + 5], 5, -701558691);
 l = md5_gg(l, o, n, m, p[g + 10], 9, 38016083); m = md5_gg(m, l, o, n, p[g + 15], 14, -660478335);
n = md5_gg(n, m, l, o, p[g + 4], 20, -405537848); o = md5_gg(o, n, m, l, p[g + 9], 5, 568446438);
l = md5_gg(l, o, n, m, p[g + 14], 9, -1019803690); m = md5_gg(m, l, o, n, p[g + 3], 14, -187363961);
 n = md5_gg(n, m, l, o, p[g + 8], 20, 1163531501); o = md5_gg(o, n, m, l, p[g + 13], 5, -1444681467);
 l = md5_gg(l, o, n, m, p[g + 2], 9, -51403784); m = md5_gg(m, l, o, n, p[g + 7], 14, 1735328473);
n = md5_gg(n, m, l, o, p[g + 12], 20, -1926607734); o = md5_hh(o, n, m, l, p[g + 5], 4, -378558);
l = md5_hh(l, o, n, m, p[g + 8], 11, -2022574463); m = md5_hh(m, l, o, n, p[g + 11], 16, 1839030562);
 n = md5_hh(n, m, l, o, p[g + 14], 23, -35309556); o = md5_hh(o, n, m, l, p[g + 1], 4, -1530992060);
 l = md5_hh(l, o, n, m, p[g + 4], 11, 1272893353); m = md5_hh(m, l, o, n, p[g + 7], 16, -155497632);
n = md5_hh(n, m, l, o, p[g + 10], 23, -1094730640); o = md5_hh(o, n, m, l, p[g + 13], 4, 681279174);
l = md5_hh(l, o, n, m, p[g + 0], 11, -358537222); m = md5_hh(m, l, o, n, p[g + 3], 16, -722521979);
n = md5_hh(n, m, l, o, p[g + 6], 23, 76029189); o = md5_hh(o, n, m, l, p[g + 9], 4, -640364487);
l = md5_hh(l, o, n, m, p[g + 12], 11, -421815835); m = md5_hh(m, l, o, n, p[g + 15], 16, 530742520);
n = md5_hh(n, m, l, o, p[g + 2], 23, -995338651); o = md5_ii(o, n, m, l, p[g + 0], 6, -198630844);
l = md5_ii(l, o, n, m, p[g + 7], 10, 1126891415); m = md5_ii(m, l, o, n, p[g + 14], 15, -1416354905);
n = md5_ii(n, m, l, o, p[g + 5], 21, -57434055); o = md5_ii(o, n, m, l, p[g + 12], 6, 1700485571);
l = md5_ii(l, o, n, m, p[g + 3], 10, -1894986606); m = md5_ii(m, l, o, n, p[g + 10], 15, -1051523);
n = md5_ii(n, m, l, o, p[g + 1], 21, -2054922799); o = md5_ii(o, n, m, l, p[g + 8], 6, 1873313359);
 l = md5_ii(l, o, n, m, p[g + 15], 10, -30611744); m = md5_ii(m, l, o, n, p[g + 6], 15, -1560198380);
n = md5_ii(n, m, l, o, p[g + 13], 21, 1309151649); o = md5_ii(o, n, m, l, p[g + 4], 6, -145523070);
l = md5_ii(l, o, n, m, p[g + 11], 10, -1120210379); m = md5_ii(m, l, o, n, p[g + 2], 15, 718787259);
n = md5_ii(n, m, l, o, p[g + 9], 21, -343485551); o = safe_add(o, j); n = safe_add(n, h);
m = safe_add(m, f); l = safe_add(l, e) } return Array(o, n, m, l) }
function md5_cmn(h, e, d, c, g, f) { return safe_add(bit_rol(safe_add(safe_add(e, h),
safe_add(c, f)), g), d) } function md5_ff(g, f, k, j, e, i, h)
{ return md5_cmn((f & k) | ((~f) & j), g, f, e, i, h) } function md5_gg(g, f, k, j, e, i, h)
{ return md5_cmn((f & j) | (k & (~j)), g, f, e, i, h) } function md5_hh(g, f, k, j, e, i, h)
{ return md5_cmn(f ^ k ^ j, g, f, e, i, h) } function md5_ii(g, f, k, j, e, i, h)
{ return md5_cmn(k ^ (f | (~j)), g, f, e, i, h) } function safe_add(a, d)
{ var c = (a & 65535) + (d & 65535); var b = (a >> 16) + (d >> 16) + (c >> 16);
return (b << 16) | (c & 65535) } function bit_rol(a, b) { return (a << b) | (a >>> (32 - b)) }

实现： 

<script src="md5-min.js" type="text/javascript"></script>
 <script type="text/javascript">
 alert('abc经过md5加密后结果为：\n'+hex_md5('abc'));
 </script>

六、php实现md5加密：

echo strtoupper(md5('abc')) ;

发表在 Asp.Net, JavaScript, MySQL, Oracle, PHP, SQL Server | 一条评论

mysql数据管理

发表于 2010 年 7 月 31 日 由 惜分飞

本人的兴趣一直都是oracle数据库的管理，最近公司上电子商务系统，现在还处于测试状态，不出意外应该没有大问题，因为程序是 php+mysql的，这就涉及到程序的维护、数据库的维护和管理。虽然mysql数据库在一般情况下，直接复制数据库下的data文件夹下面的数据库名 的文件夹可以备份

如 上图，这里我选中的每一文件夹其实是mysql数据库的一个数据库，只要直接复制就可以备份相应的数据库了，但是考虑到系统实时性可能比较高，如果这样备 份，肯定会导致数据的丢失，还有就是因为系统里面的数据库有60多张表，一张表又有三个文件，如果数据量大的时候，备份起来时间比较长

所有决定写一个脚本使用命令mysqldump+windows系统的任务自动定时执行备份，并实现为了方便转移，打包压缩功能

@echo off
echo 批发网中文版和英文版数据库备份
echo.
echo 程序启动时间：%date% %time%
echo.
set "Name=%date:~,4%%date:~5,2%%date:~8,2%_%random%"
set "Path=e:\MySql_BackUp\%Name%"
md %Path%
echo 保存备份数据库文件路径为：%Path%
echo ***********************************************************
echo.
echo %time%：开始备份db_glasses_en数据库……
echo.
e:\AppServ\MySQL\bin\mysqldump --opt -u root --password=4020894 -F -B --default-character-set=utf8 db_glasses_en_bak > %Path%\db_glasses_en_bak.sql
echo 文件：%Path%\db_glasses_en_bak.sql
echo %time%：db_glasses_en数据库备份完成
echo.
echo ***********************************************************
echo.
echo %time%：开始备份db_glasses_cn数据库……
echo.
e:\AppServ\MySQL\bin\mysqldump --opt -u root --password=4020894 -F -B --default-character-set=utf8 db_glasses_cn > %Path%\db_glasses_cn_bak.sql
echo 文件：%Path%\db_glasses_cn_bak.sql
echo %time%：db_glasses_cn数据库备份完成
echo.
echo ***********************************************************
echo.
echo 批发网中英文数据库备份完成……
echo.
echo ***********************************************************
echo.
echo %time%：备份数据库进行压缩……
cd C:\Program Files\WinRAR
WinRAR.exe a -m5 e:\MySql_BackUp\%Name%.rar %Path%
echo.
echo %time%:e:\MySql_BackUp\%Name%.rar压缩完成
echo 请检查文件并做好数据转移……
echo.
echo 程序结束时间：%date% %time%
echo.
echo ***********************************************************
pause

通过上面的脚本实现数据库的备份，结果如下图：

上图即为执行备份后的结果，至于mysql的备份命令mysqldump的相关参数请见mysql的相关文档。

补充说明：

在 mssql里面日志是自动启动的；在oracle归档日志默认情况下是关闭的，可以在mount模式下，使用alter database archivelog开启重做日志归档模式；在mysql中，日志默认情况下也是关闭的，但是它是通过修改my.ini配置文件实现修改的，具体参数如 下：

log-bin=F:\AppServ\MySQL\log\log_bin.log
log=F:\AppServ\MySQL\log\log.log
log-update= F:\AppServ\MySQL\log\update.log
log-slow-queries=F:\AppServ\MySQL\log\mysql-slow.log
log-error=F:\AppServ\MySQL\log\error.log

各日志参数说明：

错误日志：   -log-err
查询日志：   -log
慢查询日志:     -log-slow-queries
更新日志:     -log-update
二进制日志：   -log-bin

注：如果日志参数后面直接跟文件名，那日志文件会放置在mysql/data文件夹下

发表在 MySQL | 评论关闭