目录结构
注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：
1、参考书籍：《Oracle Database SQL Language Reference》
2、参考书籍：《PostgreSQL中文手册》
3、EDB Postgres Advanced Server User Guides，点击前往
4、PostgreSQL数据库仓库链接，点击前往
5、PostgreSQL中文社区，点击前往
1、本文内容全部来源于开源社区 GitHub和以上博主的贡献，本文也免费开源（可能会存在问题，评论区等待大佬们的指正）
2、本文目的：开源共享 抛砖引玉 一起学习
3、本文不提供任何资源 不存在任何交易 与任何组织和机构无关
4、大家可以根据需要自行 复制粘贴以及作为其他个人用途，但是不允许转载 不允许商用 （写作不易，还请见谅 💖）
Oracle数据库数据库的SQL ID的底层计算原理分析
- 文章快速说明索引
- 背景说明案例展示
- 底层实现案例分析
文章快速说明索引
学习目标：
目的：接下来这段时间我想做一些兼容Oracle数据库Real Application Testing (即：RAT)上的一些功能开发，本专栏这里主要是学习以及介绍Oracle数据库功能的使用场景、原理说明和注意事项等，基于PostgreSQL数据库的功能开发等之后 由新博客进行介绍和分享！
学习内容：（详见目录）
1、Oracle数据库数据库的SQL ID的底层计算原理分析
学习时间：
2025年02月09日 18:06:19
学习产出：
1、Oracle数据库数据库的SQL ID的底层计算原理分析
2、CSDN 技术博客 1篇
注：下面我们所有的学习环境是Centos7+PostgreSQL15.0+Oracle19c+MySQL5.7
postgres=# select version();version
-----------------------------------------------------------------------------PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)postgres=##-----------------------------------------------------------------------------#SQL> select * from v$version; BANNER BANNER_FULL BANNER_LEGACY CON_ID
--------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0Version 19.3.0.0.0SQL>
#-----------------------------------------------------------------------------#mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.19 |
+-----------+
1 row in set (0.06 sec)mysql>
背景说明案例展示
在使用Oracle数据库的时候经常会看到SQL ID这样的东西，如下：
或者是如下SQL语句：
[oracle@local64 ~]$ sqlplus '/as sysdba'SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 7 14:10:57 2025Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select 'SONG BAOBAO' from dual;'SONGBAOBAO
-----------
SONG BAOBAOSQL> select 'SONGBAobao' from DUAL;'SONGBAOBA
----------
SONGBAobaoSQL> select sql_id, sql_text from v$sql where sql_text like '%SONG%';SQL_ID SQL_TEXT
-------------------- ----------------------------------------------------------------------------------------------------
dfrun6x61sj3g select 'SONGBAobao' from DUAL
2fsps80qfadc3 select sql_id, sql_text from v$sql where sql_text like '%SONG%'
dgs6n0z31avcp select 'SONG BAOBAO' from dualSQL>
或者通过package函数，如下：
SQL> select dbms_sqltune_util0.sqltext_to_sqlid('select ''SONG BAOBAO'' from dual'||chr(0)) sql_id from dual;SQL_ID
--------------------
dgs6n0z31avcpSQL> select dbms_sqltune_util0.sqltext_to_sqlid('select ''SONGBAobao'' from DUAL'||chr(0)) sql_id from dual;SQL_ID
--------------------
dfrun6x61sj3gSQL>
如上都是简单SQL语句，下面看一下上面AWR图的一个例子，如下：
7r7636982atn9 UPDATE INVENTORIES SET QUANTITY_ON_HAND = QUANTITY_ON_HAND - :B1 WHERE PRODUCT_ID = :B3 AND WAREHOUSE_ID = :B2
接下来，我们今天分析一下Oracle SQL_ID的生成过程以及相关注意事项，如下：
一、SQL_ID生成过程
-
计算MD5散列值：
- Oracle会对SQL_TEXT计算MD5散列值，并在SQL_TEXT末尾添加一个不可见字符
\0（空字符）如上所述。 - 这个步骤确保了即使SQL_TEXT内容相同，生成的SQL_ID也会因为末尾的空字符而不同。
- Oracle会对SQL_TEXT计算MD5散列值，并在SQL_TEXT末尾添加一个不可见字符
-
取MD5散列值的低64位(请注意大小端的问题)：
- MD5散列值是一个128位的值，Oracle只取其中的低64位（即最后8个字节）。
- 由于MD5是128位的，低64位就是最后64位（即最后8个字节）。
-
将64位分成5位一组（最后一组4位）：
- 将这64位分成13组，前12组每组5位，最后一组4位。
- 每组5位的值范围是0到31，最后一组4位的值范围是0到15。
-
Base32编码：
- 使用Base32将每组5位（或4位）的值转换为可见字符。
- Oracle使用的Base32字符集为：
0123456789abcdfghjkmnpqrstuvwxyz。 - 注意：这个字符集与标准的Base32字符集不同，标准的Base32字符集通常包含字母A-Z和数字2-7。
二、注意事项
(a) MD5散列时的空字符：
- 在计算MD5时，Oracle会在SQL_TEXT末尾添加一个空字符
\0。这意味着即使两个SQL_TEXT内容相同，生成的SQL_ID也会不同，因为末尾的空字符会导致MD5散列值不同。
(b) Little-endian问题：
- 在处理字节序时，Oracle使用的是小端序（Little-endian）。这意味着在读取字节时，低位字节在前，高位字节在后。
- 在编写程序时，需要确保正确处理字节序，特别是在处理64位值时。
© Base32字符集：
- Oracle使用的Base32字符集为：
0123456789abcdfghjkmnpqrstuvwxyz。 - 这个字符集与标准的Base32字符集不同，因此在编写程序时需要特别注意使用正确的字符集。
(d) 大数精度问题：
- 在处理64位值时，可能会遇到大数精度问题。特别是在一些编程语言中，默认的整数类型可能无法处理64位值。
- 在Perl、PHP等语言中，可以使用大数处理函数（如
GMP或BCMath扩展）来确保精度。
底层实现案例分析
SQL> select dbms_sqltune_util0.sqltext_to_sqlid('select ''SONG BAOBAO'' from dual'||chr(0)) sql_id from dual;SQL_ID
--------------------
dgs6n0z31avcpSQL>
我的环境，如下：
[oracle@local64 mydata]$ lscpu | grep -i byte
Byte Order: Little Endian
[oracle@local64 mydata]$
第一步，计算散列值
- Digest::MD5 相关函数说明，点击前往
先计算一下其md5值，如下：
[oracle@local64 mydata]$ cat mytest3.pl
use Digest::MD5 qw(md5_hex);
$stmt = "select 'SONG BAOBAO' from dual\0";
$hash_hex = md5_hex $stmt;
print "$hash_hex\n";[oracle@local64 mydata]$ perl mytest3.pl
10131690c894385807d4e0d7956d15c6
[oracle@local64 mydata]$10131690c8943858
07d4e0d7956d15c6
- 进制转换在线，点击前往
其二进制转换为：
00010000000100110001011010010000110010001001010000111000010110000000011111010100111000001101011110010101011011010001010111000110
第二步，取低64位
07d4e0d7956d15c60000011111010100111000001101011110010101011011010001010111000110
第三步，转换为易读字符串
Oracle使用了Base32将字节流转换为可见字符，一一对应如下：
| 字节流十进制 | 00 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 | 13 | 14 | 15 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 字符 | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | a | b | c | d | f | g |
| 字节流十进制 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 字符 | h | j | k | m | n | p | q | r | s | t | u | v | w | x | y | z |
注： 这里给大家推荐一个好用的网站(可以直接复制粘贴到markdown)：
- 表格转换工具，点击前往
0000011111010100111000001101011110010101011011010001010111000110# 划分一下，如下：
00000111 11010100 11100000 11010111
10010101 01101101 00010101 11000110# 因为是小端11010111 11100000 11010100 00000111
11000110 00010101 01101101 10010101# 合并
1101011111100000110101000000011111000110000101010110110110010101# 其十六进制
d7e0d407c6156d95
然后切分，以及转换 如下：
1101 01111 11000 00110 10100 00000 11111 00011 00001 01010 11011 01100 10101
13 15 24 6 20 0 31 3 1 10 27 12 21
d g s 6 n 0 z 3 1 a v c pdgs6n0z31avcp
最后看一下转换一个SQL的SQLID逻辑，如下：
#!/usr/bin/perl -w
use Digest::MD5 qw(md5 md5_hex md5_base64);
use Math::BigInt;
my $stmt = "select 'SONG BAOBAO' from dual\0";
my $hash = md5 $stmt;
my($a,$b,$msb,$lsb) = unpack("V*",$hash);
print "a is $a, b is $b; msb is $msb, lsb is $lsb\n";my $sqln = $msb*(2**32)+$lsb;
my $stop = log($sqln) / log(32) + 1;
print "sqln is $sqln, stop is $stop\n";my $sqlid = '';
my $charbase32 = '0123456789abcdfghjkmnpqrstuvwxyz';
my @chars = split '', $charbase32;
for($i=0; $i < $stop-1; $i++){my $x = Math::BigInt->new($sqln);my $seq = $x->bdiv(32**$i)->bmod(32);$sqlid = $chars[$seq].$sqlid;
}
print "SQL is:\n $stmt \nSQL_ID is\n $sqlid\n"; 执行一下，如下：
[oracle@local64 mydata]$ perl mytest1.pl
a is 2417365776, b is 1480103112; msb is 3621835783, lsb is 3323293077
sqln is 15555666242790845845, stop is 13.7508147978182
SQL is:select 'SONG BAOBAO' from dual
SQL_ID isdgs6n0z31avcp
[oracle@local64 mydata]$
解释一下，上面sqln = 15555666242790845845，该十进制的十六进制转换，如下：
[oracle@local64 mydata]$ cat convert_uint64_to_str.c
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <stdint.h>int main()
{const char *map = "0123456789abcdfghjkmnpqrstuvwxyz";int i = 0;char str[14] = {0};uint64_t sqlid = (uint64_t)15555666242790845845ULL;for(; i<13;++i){int j = (sqlid >> (60 - i * 5)) & 0x1F;str[i] = map[j];}printf("str is %s\n", str);return 0;
}
[oracle@local64 mydata]$
[oracle@local64 mydata]$ gcc -o convert convert_uint64_to_str.c -g -O0
[oracle@local64 mydata]$
[oracle@local64 mydata]$ ./convert
str is dgs6n0z31avcp
[oracle@local64 mydata]$
若是用C语言实现上面perl的效果，如下：
[oracle@local64 mydata]$ cat mytest2.c
#include <stdio.h>
#include <string.h>
#include <openssl/md5.h>
#include <math.h>
#include <stdint.h>// 用于计算 MD5 的函数
void md5_hash(const unsigned char *input, size_t length, unsigned char *output)
{MD5_CTX mdContext;MD5_Init(&mdContext);MD5_Update(&mdContext, input, length);MD5_Final(output, &mdContext);
}int main()
{int step = 0;// 包含 \0 的原始字节数组const unsigned char stmt[] = "select 'SONG BAOBAO' from dual\0";size_t stmt_len = sizeof(stmt) - 1; // 计算不包括末尾 \0 的实际长度// 用于存放 MD5 哈希结果unsigned char hash[MD5_DIGEST_LENGTH];md5_hash(stmt, stmt_len, hash); // 计算 MD5// 解包 MD5 哈希值uint32_t a = (hash[0] << 0) | (hash[1] << 8) | (hash[2] << 16) | (hash[3] << 24);uint32_t b = (hash[4] << 0) | (hash[5] << 8) | (hash[6] << 16) | (hash[7] << 24);uint32_t msb = (hash[8] << 0) | (hash[9] << 8) | (hash[10] << 16) | (hash[11] << 24);uint32_t lsb = (hash[12] << 0) | (hash[13] << 8) | (hash[14] << 16) | (hash[15] << 24);printf("a is %u, b is %u; msb is %u, lsb is %u\n", a, b, msb, lsb);// 计算 sqln 和 stopuint64_t sqln = ((uint64_t)msb << 32) + lsb;double stop = log((double)sqln) / log(32.0) + 1;printf("sqln is %llu, stop is %.06f\n", sqln, stop);// Base32 字符集char charbase32[] = "0123456789abcdfghjkmnpqrstuvwxyz";char sqlid[100] = {0};step = (int)ceil(stop);// 生成 SQL_IDfor (int i = 0; i < step - 1; i++){unsigned long long pow_result = pow(32, i);unsigned long long x = sqln;unsigned long long div_result = x / pow_result;unsigned int seq = div_result % 32;// 插入到 sqlid 字符串的前面memmove(sqlid + 1, sqlid, strlen(sqlid) + 1);sqlid[0] = charbase32[seq];}printf("SQL is:\n %s\nSQL_ID is\n %s\n", stmt, sqlid);return 0;
}
[oracle@local64 mydata]$ gcc -o test2 mytest2.c -lssl -lcrypto -lm -g -O0
[oracle@local64 mydata]$
[oracle@local64 mydata]$ ./test2
a is 2417365776, b is 1480103112; msb is 3621835783, lsb is 3323293077
sqln is 15555666242790845845, stop is 13.750815
SQL is:select 'SONG BAOBAO' from dual
SQL_ID isdgs6n0z31avcp
[oracle@local64 mydata]$
