目录

Oracle数据库中插入BLOB字段

Oracle数据库中插入CLOB、NCLOB数据的方法

Oracle CLOB 数据插入方式

Oracle NCLOB 数据插入方式

MySQL数据库插入blob数据方法


数据库中插入大字段数据的方法。

Oracle数据库中插入BLOB字段

1./opt/pic/目录下存放图片文件

 2.先创建表,存储图片的字段为BLOB类型,创建表可以用sql也可以用navicat等工具,先插入数据填入其他字段,BLOB为null不填

  CREATE TABLE PICTURE_SRC ( "ID" NUMBER NOT NULL ENABLE, "IDENTIFICATION" VARCHAR2(255), "PROVINCE" VARCHAR2(255), 

"NAME" VARCHAR2(255), "SEX" VARCHAR2(255), "PIC" BLOB, "PICOLD" BLOB, PRIMARY KEY ("ID");

 3.创建图片目录,images为目录名,如果Oracle安装再windows上,则目录格式为'd:\pic\',先将图片文件保存到/opt/pic/目录下,然后在Linux的sql命令行下执行:

  create or replace directory "DIR" as '/opt/pic/';

 4.通过update方式插入图片(BLOB字段)

 方法1:直接执行代码块插入BLOB数据(可在navicat, dbeaver, sqlplus中执行)

 declare
   l_bfile bfile;
   l_blob blob;
 begin
   update PICTURE_SRC set PIC=empty_blob() where ID=5 
   return PIC into l_blob;
   l_bfile:=bfilename('DIR','5.jpg');
   dbms_lob.open(l_bfile,dbms_lob.file_readonly);
   dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));
   dbms_lob.close(l_bfile);
   commit;
 end;
 /
-- 注意:sqlplus 中执行必须包含末尾的"/",navicat或debeaver工具中不能包含否则会报错

 方法2:创建过程插入(在命令行执行)

 create or replace procedure img_insert(tg_id NUMBER ,tg_filename VARCHAR2) as
   l_bfile bfile;
   l_blob blob;
 begin
   update PICTURE_SRC set PIC=empty_blob() where ID=tg_id return PIC into l_blob;
   l_bfile:=bfilename('DIR',tg_filename);
   dbms_lob.open(l_bfile,dbms_lob.file_readonly);
   dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));
   dbms_lob.close(l_bfile);
   commit;
 --end img_insert;

 end;

--下面命令exec只能在命令行执行,不能在navicat中执行,否则会报错ORA-00900: invalid SQL statement

SQL> exec img_insert(5,'5.jpg');

PL/SQL procedure successfully completed.

注意:红色标注部分,大小写必须一致,否则前者如果不加双引号强制大写默认为大写,后者引用时为大小写敏感。如果两者大小写不一致,会导致报错找不到文件目录:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 1014
ORA-06512: at line 8

测试02:

sqlplus  / as sysdba
conn test/1q2w3e

-- DROP TABLE TEST.BLOB_TEST01;
CREATE TABLE TEST.BLOB_TEST01 (
    ID NUMBER(38,0),
    INFO VARCHAR2(255),
    CNT NUMBER(38,0),
    C_BLOB BLOB,
    CONSTRAINT BLOB_TEST01_PK PRIMARY KEY (ID)
);

INSERT INTO TEST.BLOB_TEST01 (ID, INFO, CNT, C_BLOB) VALUES (1, 'aaa', NULL, NULL);
INSERT INTO TEST.BLOB_TEST01 (ID, INFO) VALUES (2, 'bbb');
-- select * from TEST.BLOB_TEST01;

create or replace directory "DIR" as '/home/oracle/blob/';

declare
  l_bfile bfile;
  l_blob blob;
begin
  update BLOB_TEST01 set C_BLOB=empty_blob() where ID=1 
  return C_BLOB into l_blob;
  l_bfile:=bfilename('DIR','a.zip');
  dbms_lob.open(l_bfile,dbms_lob.file_readonly);
  dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));
  dbms_lob.close(l_bfile);
  commit;
end;
/
 
select ID,C_BLOB from BLOB_TEST01 where ID=1;


执行效果如下:
SQL> create or replace directory "DIR" as '/home/oracle/blob/';

Directory created.

SQL> declare
  2    l_bfile bfile;
  3    l_blob blob;
  4  begin
  5    update BLOB_TEST01 set C_BLOB=empty_blob() where ID=1 
  6    return C_BLOB into l_blob;
  7    l_bfile:=bfilename('DIR','a.zip');
  8    dbms_lob.open(l_bfile,dbms_lob.file_readonly);
  9    dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));
 10    dbms_lob.close(l_bfile);
 11    commit;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> select ID,C_BLOB from BLOB_TEST01 where ID=1;

        ID
----------
C_BLOB
--------------------------------------------------------------------------------
         1
504B03040A0000000000AB5CEB4A51109F284D0E00004D0E00000A000000315F332E366B2E6A7067
FFD8FFE000104A46494600010101006000600000FFDB004300080606070605080707070909080A0C


SQL> 
 

Oracle数据库中插入CLOB、NCLOB数据的方法

Oracle CLOB 数据插入方式


--------------------------------------------------------------------------
-- Oracle CLOB 数据插入方式
DROP TABLE TEST.CLOB_TEST04;
CREATE TABLE TEST.CLOB_TEST04
( ID NUMBER(38,0),
INFO VARCHAR2(255),
CNT NUMBER(38,0),
C_CLOB CLOB,
CONSTRAINT CLOB_TEST04_PK PRIMARY KEY (ID)
);

SELECT table_name,dbms_metadata.get_ddl('TABLE','CLOB_TEST04') AS ddl from dual,user_tables where table_name='CLOB_TEST04';
DELETE FROM TEST.CLOB_TEST04;
INSERT INTO TEST.CLOB_TEST04 (ID, INFO, CNT, C_CLOB) VALUES (1, 'aaa', NULL, 'test_string_xxx_测试_');
INSERT INTO TEST.CLOB_TEST04 (ID, INFO, CNT, C_CLOB) VALUES (2, 'bbb', NULL, TO_CLOB('test_string_xxx_测试_'));
INSERT INTO TEST.CLOB_TEST04 (ID, INFO, CNT, C_CLOB) VALUES (3, 'ccc', NULL, NULL);
SELECT ID,INFO,CNT,C_CLOB,DBMS_LOB.getlength(C_CLOB) AS LEN_C, lengthb(to_char(C_CLOB)) AS LEN_B FROM TEST.CLOB_TEST04;

-- 简单插入:DBMS_LOB.WRITE(dst, length, offset_start_position, src);
DECLARE
  v_lob_loc CLOB;
  v_str VARCHAR2(100);
BEGIN
  v_str := 'test_string_xxx_测试_';
  DBMS_LOB.createtemporary(v_lob_loc,TRUE);
  DBMS_LOB.write(v_lob_loc, length(v_str), 1, v_str);
  DBMS_OUTPUT.put_line(v_lob_loc||' 字符长度:'||DBMS_LOB.getlength(v_lob_loc)||' 字节长度:'||lengthb(to_char(v_lob_loc)));
  UPDATE TEST.CLOB_TEST04 SET C_CLOB=v_lob_loc WHERE ID=3;
  INSERT INTO TEST.CLOB_TEST04(ID, INFO, CNT, C_CLOB) VALUES (4, 'ddd', NULL, v_lob_loc);
  COMMIT;
END;
/

SELECT ID,INFO,CNT,C_CLOB,DBMS_LOB.getlength(C_CLOB) AS LEN_C, lengthb(to_char(C_CLOB)) AS LEN_B FROM TEST.CLOB_TEST04;


-- 循环追加:DBMS_LOB.append(dst,src);
DECLARE
  v_lob_loc CLOB;
  v_str VARCHAR2(100);
BEGIN
  v_str := 'test_string_xxx_测试_';
  DBMS_LOB.createtemporary(v_lob_loc,TRUE);
  FOR i IN 1..3 LOOP
    DBMS_LOB.append(v_lob_loc, v_str);
    DBMS_OUTPUT.put_line(v_lob_loc||' 字符长度:'||DBMS_LOB.getlength(v_lob_loc)||' 字节长度:'||lengthb(to_char(v_lob_loc)));
  END LOOP;
  UPDATE TEST.CLOB_TEST04 SET C_CLOB=v_lob_loc WHERE ID=3;
  INSERT INTO TEST.CLOB_TEST04(ID, INFO, CNT, C_CLOB) VALUES (4, 'ddd', NULL, v_lob_loc);
  COMMIT;
END;
/

SELECT ID,INFO,CNT,C_CLOB,DBMS_LOB.GETLENGTH(C_CLOB) AS LEN_C, lengthb(to_char(C_CLOB)) AS LEN_B FROM TEST.CLOB_TEST04;

---------------------------------------------------------------------------

Oracle NCLOB 数据插入方式

-- Oracle NCLOB 数据插入方式
DROP TABLE TEST.NCLOB_TEST04;
CREATE TABLE TEST.NCLOB_TEST04
( ID NUMBER(38,0),
INFO VARCHAR2(255),
CNT NUMBER(38,0),
C_NCLOB NCLOB,
CONSTRAINT NCLOB_TEST04_PK PRIMARY KEY (ID)
);

SELECT table_name,dbms_metadata.get_ddl('TABLE','NCLOB_TEST04') AS ddl from dual,user_tables where table_name='NCLOB_TEST04';
DELETE FROM TEST.NCLOB_TEST04;
INSERT INTO TEST.NCLOB_TEST04 (ID, INFO, CNT, C_NCLOB) VALUES (1, 'aaa', NULL, 'test_string_xxx_测试_');
INSERT INTO TEST.NCLOB_TEST04 (ID, INFO, CNT, C_NCLOB) VALUES (2, 'bbb', NULL, TO_NCLOB('test_string_xxx_测试_'));
INSERT INTO TEST.NCLOB_TEST04 (ID, INFO, CNT, C_NCLOB) VALUES (3, 'ccc', NULL, NULL);
SELECT ID,INFO,CNT,C_NCLOB,DBMS_LOB.getlength(C_NCLOB) AS LEN_C, lengthb(to_char(C_NCLOB)) AS LEN_B FROM TEST.NCLOB_TEST04;

-- 简单插入:DBMS_LOB.WRITE(dst, length, offset_start_position, src);
DECLARE
  v_lob_loc CLOB;
  v_str VARCHAR2(100);
BEGIN
  v_str := 'test_string_xxx_测试_';
  DBMS_LOB.createtemporary(v_lob_loc,TRUE);
  DBMS_LOB.write(v_lob_loc, length(v_str), 1, v_str);
  DBMS_OUTPUT.put_line(v_lob_loc||' 字符长度:'||DBMS_LOB.getlength(v_lob_loc)||' 字节长度:'||lengthb(to_char(v_lob_loc)));
  UPDATE TEST.NCLOB_TEST04 SET C_NCLOB=v_lob_loc WHERE ID=3;
  INSERT INTO TEST.NCLOB_TEST04(ID, INFO, CNT, C_NCLOB) VALUES (4, 'ddd', NULL, v_lob_loc);
  COMMIT;
END;
/

SELECT ID,INFO,CNT,C_NCLOB,DBMS_LOB.getlength(C_NCLOB) AS LEN_C, lengthb(to_char(C_NCLOB)) AS LEN_B FROM TEST.NCLOB_TEST04;


-- 循环追加:DBMS_LOB.append(dst,src);
DECLARE
  v_lob_loc CLOB;
  v_str VARCHAR2(100);
BEGIN
  v_str := 'test_string_xxx_测试_';
  DBMS_LOB.createtemporary(v_lob_loc,TRUE);
  FOR i IN 1..3 LOOP
    DBMS_LOB.append(v_lob_loc, v_str);
    DBMS_OUTPUT.put_line(v_lob_loc||' 字符长度:'||DBMS_LOB.getlength(v_lob_loc)||' 字节长度:'||lengthb(to_char(v_lob_loc)));
  END LOOP;
  UPDATE TEST.NCLOB_TEST04 SET C_NCLOB=v_lob_loc WHERE ID=3;
  INSERT INTO TEST.NCLOB_TEST04(ID, INFO, CNT, C_NCLOB) VALUES (4, 'ddd', NULL, v_lob_loc);
  COMMIT;
END;
/

SELECT ID,INFO,CNT,C_NCLOB,DBMS_LOB.GETLENGTH(C_NCLOB) AS LEN_C, lengthb(to_char(C_NCLOB)) AS LEN_B FROM TEST.NCLOB_TEST04;

-------------------------------------------------

MySQL数据库插入blob数据方法

INSERT INTO `usertest01`.`test_table01` (`id`, `col_varchar`, `col_blob`) VALUES (1, 'sss', UNHEX(HEX('abcd')));
或者
INSERT INTO `usertest01`.`test_table01` (`id`, `col_varchar`, `col_blob`) VALUES (1, 'sss', UNHEX('61626364'));
或者
INSERT INTO `usertest01`.`test_table01` (`id`, `col_varchar`, `col_blob`) VALUES (1, 'sss', UNHEX(61626364));

select hex(col_blob) from `usertest01`.`test_table01`;

MySQL插入插入图片:
select load_file('/var/lib/mysql-files//a.jpg');
select LENGTH(load_file('/var/lib/mysql-files/a.jpg'));
insert into test_table01(id,info,col_blob) values (2,'pic',load_file('/var/lib/mysql-files/a.jpg'));
select @@secure_file_priv, @@max_allowed_packet, LENGTH(load_file('/var/lib/mysql-files/a.jpg'));

-- 如果插入报错 Data too long for column 'xxx' at row 1,可能是目的字段长度不够,需要调整字段类型
alter table test_table01 modify column col_blob MEDIUMBLOB DEFAULT NULL COMMENT 'BLOB测试字段';

说明:
HEX()函数:将一个字符串或数字转换为十六进制格式的字符串
UNHEX()函数:把十六进制格式的字符串转化为二进制的数据

select hex('abcd');
-- 结果为 61626364;

select unhex('61626364');
或者
select unhex(61626364);
-- 结果为 abcd


load_file 函数使用的条件和限制:
1,若没有加载成功会返回空(NULL)
2,必须设置参数 secure_file_priv,加载的文件必须需要放在该参数指定的目录
3,load的文件的大小(LENGTH)不能大于 max_allowed_packet
4,相关查询语句:  
  select @@secure_file_priv, @@max_allowed_packet, LENGTH(load_file('/var/lib/mysql-files/a.jpg'));
  -- 我的查询结果: 
  /var/lib/mysql-files/    4194304    372840
5,如果报错:SQL 错误 [1406] [22001]: Data truncation: Data too long for column 'col_blob' at row 1
  目的表字段类型长度不够,数据超出了字段的最大长度,需要调整目的表字段的长度,我最初设置是BLOB类型,后轮调整为
  不同类型的BLOB的长度参考:
  TINYBLOB ≈ 255 bytes, 
  BLOB: 大约 64KB,65535(2^16-1)
  MEDIUMBLOB: 大约 16,777,215(2^24–1)
  LONGBLOB: 大约 4GB,4,294,967,295(2^32–1)
  修改表字段大小:
  alter table test_table01 modify column col_blob MEDIUMBLOB DEFAULT NULL COMMENT 'BLOB测试字段';

-- 100英文+100中文
insert into test_table01(id,col_varchar,col_blob) values (2,'text',UNHEX(HEX('test567890test567890test567890test567890test567890test567890test567890test567890test567890test567890测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试')));


-- 字段内容及字段长度相关查询

select col_blob from test_table01;
select hex(col_blob) from test_table01;

select length(hex(col_blob)) from test_table01;

select length(col_blob) from test_table01;

select char_length(col_blob) from test_table01;

select octet_length(col_blob) from test_table01;

select bit_length(col_blob) from test_table01;

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐