说点JSON使用的注意事项
JSON这种数据类型在关系型数据库中已经是标配了。如果不是深度应用比如游戏那种的确是离开不了MongoDB，其他的一些简单场景的话Oracle MySQL PostgreSQL都有JSON。今天仅仅说一下Oracle的JSON在使用上的一些问题。
开发人员提出他的场景中需要使用JSON（这个之前我推荐过，现在Oracle和MySQL的JSON都普遍使用，毕竟有了这个减少了数据库的表的变更也是好的）。我的原则是不参与业务逻辑的、不参与运算的、不用来当where条件的可以放在JSON中，反之不要放进来。不过这次开发给我提出了一个问题，就是看到官方介绍（开发能去看数据库官方介绍，其实很不容易，为这位开发同学点赞。我都没怎么去看过）
考虑到性能问题，开发打算用Varchar2的数据类型。不过这个类型有限制，只有4K大小。开发表示也够用。只是想问问万一超过4K怎么做？我第一反应就是用CLOB，因为我在Oracle12C的时候就是用CLOB来完成JSON的。那么这个是不是可以改变类型呢？做个实验。
SQL> create table x (id int,j varchar2(4000) CONSTRAINT ensure_json CHECK (j IS JSON));
表已创建。
SQL> desc x;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
J VARCHAR2(4000)
创建了一个VARCHAR2类型的JSON的表。去写入一条数据。
SQL> insert into x values (1,'{"tel" : "137", "company": "ouyeel", "name" : "张","address":"宝山"}');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from x;
ID
----------
J
--------------------------------------------------------------------------------
1
{"tel" : "137", "company": "ouyeel", "name" : "张","address":"宝山"}
这里要注意，表名一定要有别名，当有了别名t以后查询正常输出。
SQL> select j.tel from x;
select j.tel from x
*
第 1 行出现错误:
ORA-00904: "J"."TEL": 标识符无效
SQL> select x.j.tel from x;
select x.j.tel from x
*
第 1 行出现错误:
ORA-00904: "X"."J"."TEL": 标识符无效
SQL> select t.j.tel from x t;
TEL
--------------------------------------------------------------------------------
137
再创建一个CLOB的JSON，看看能不能数据迁移？
SQL> create table x2 (id int,j clob CONSTRAINT ensure_json2 CHECK (j IS JSON));
表已创建。
SQL> insert into x2 values (1,'{"tel" : "138", "company": "ouyeel", "name" : "张2","address":"宝山"}');
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into x2 select * from x;
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from x2 t;
ID
----------
J
--------------------------------------------------------------------------------
1
{"tel" : "138", "company": "ouyeel", "name" : "张2","address
":"宝山"}
1
{"tel" : "137", "company": "ouyeel", "name" : "张","address"
:"宝山"}
实测可以迁移。那么也就是可以字段赋值了。在VARCHAR2的JSON表上再增加一个CLOB的JSON列。
SQL> alter table x add j_clob clob CONSTRAINT ensure_json3 CHECK (j_clob IS JSON);
表已更改。
SQL> update x set j_clob=j;
已更新 1 行。
SQL> commit;
提交完成。
SQL> select * from x t;
ID
----------
J
--------------------------------------------------------------------------------
J_CLOB
--------------------------------------------------------------------------------
1
{"tel" : "137", "company": "ouyeel", "name" : "张","address":"宝山"}
{"tel" : "137", "company": "ouyeel", "name" : "张","address"
:"宝山"}
可以看出，迁移数据没有问题。但是这个做法有点麻烦。其实数据库可以定义VARCHAR2类型到32K。只是这个方法理论上可行，但是实际上不推荐大家做。
因为需要修改数据库参数，这个参数还是静态参数，需要重启生效（这个知识点很少地方介绍到）
SQL> show parameter MAX_STRING_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string STANDARD
SQL>
连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
修改参数前备份参数，这个很重要。
修改参数前备份参数，这个很重要。
修改参数前备份参数，这个很重要。
SQL> alter system set max_string_size=EXTENDED scope=both;
alter system set max_string_size=EXTENDED scope=both
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效 ORA-02095:
无法修改指定的初始化参数
SQL> alter system set max_string_size=EXTENDED scope=spfile;
系统已更改。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 1.0133E+10 bytes
Fixed Size 14416848 bytes
Variable Size 8355053568 bytes
Database Buffers 1744830464 bytes
Redo Buffers 19136512 bytes
数据库装载完毕。
数据库已经打开。
SQL> alter pluggable database all open;
alter pluggable database all open
*
第 1 行出现错误:
ORA-14694: 数据库必须处于 UPGRADE 模式下才能开始 MAX_STRING_SIZE 迁移
修改以后还带了一些问题。
尽管我执行了相关的命令但是最终还是回到了Ora-14694的这里。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup upgrade;
ORACLE 例程已经启动。
Total System Global Area 1.0133E+10 bytes
Fixed Size 14416848 bytes
Variable Size 8355053568 bytes
Database Buffers 1744830464 bytes
Redo Buffers 19136512 bytes
数据库装载完毕。
数据库已经打开。
SQL> @/u01/app/oracle/product/19.3.0/db/rdbms/admin/utl32k.sql
会话已更改。
会话已更改。
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database has not been opened for UPGRADE.
DOC>
DOC> Perform a "SHUTDOWN ABORT" and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
未选定行
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database does not have compatible >= 12.0.0
DOC>
DOC> Set compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
PL/SQL 过程已成功完成。
会话已更改。
已更新 0 行。
提交完成。
系统已更改。
PL/SQL 过程已成功完成。
提交完成。
系统已更改。
会话已更改。
会话已更改。
表已创建。
表已创建。
表已创建。
表被截断。
已创建 0 行。
PL/SQL 过程已成功完成。
STARTTIME
--------------------------------------------------------------------------------
11/29/2023 10:06:31.825868000
PL/SQL 过程已成功完成。
没有错误。
PL/SQL 过程已成功完成。
会话已更改。
会话已更改。
已创建 0 行。
未选定行
未选定行
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if we encountered an error while modifying a column to
DOC> account for data type length change as a result of enabling or
DOC> disabling 32k types.
DOC>
DOC> Contact Oracle support for assistance.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
PL/SQL 过程已成功完成。
PL/SQL 过程已成功完成。
提交完成。
程序包已变更。
会话已更改。
由于修改失败，好在备份了参数，用原来参数启动数据库。所以将VARCHAR2变成32K的方案不推荐正式环境使用。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/637517/viewspace-2998071/ ，如需转载，请注明出处，否则将追究法律责任。
