說點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的方案不推薦正式環境使用。