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的方案不推荐正式环境使用。
