【 使用环境 】测试环境
【 OB or 其他组件 】OceanBase
【 使用版本 】5.7.25-OceanBase_CE-v4.3.5.3
【问题描述】通过load data local infile写入数据，使用了set 子句对空值进行转换,报错提示set expression not supported
sql: load data local infile’/data/mastercom/app/backend/logs/broadband-open/interface/BAK_Interface_log_127.0.0.1_202512161732.log’into table mtapp_mng_center.tb_服务器性能监控_数据_原始log_url_dd_251216 fields terminated by ‘$$’ ENCLOSED BY ‘’’’ ESCAPED BY ‘’ lines terminated by ’
’ IGNORE 0 LINES (TIME,USERID,LOGIN_CODE,IP,URL,INTERFACE_NAME,LOGTYPE,IMSI,IMEI,VERSION,MODEL,OSVERSION,NETWORKTYPE,APN,LAC,CI,FUNC_NAME,FUNC_FLAG,REMARK,RETCODE,ERROR,DURATION,SERVERIP,THREAD_NAME,SESSION_ID,功能ID) set TIME=if(TIME=’’,NULL,TIME),USERID=if(USERID=’’,NULL,USERID),LOGIN_CODE=if(LOGIN_CODE=’’,NULL,LOGIN_CODE),IP=if(IP=’’,NULL,IP),URL=if(URL=’’,NULL,URL),INTERFACE_NAME=if(INTERFACE_NAME=’’,NULL,INTERFACE_NAME),LOGTYPE=if(LOGTYPE=’’,NULL,LOGTYPE),IMSI=if(IMSI=’’,NULL,IMSI),IMEI=if(IMEI=’’,NULL,IMEI),VERSION=if(VERSION=’’,NULL,VERSION),MODEL=if(MODEL=’’,NULL,MODEL),OSVERSION=if(OSVERSION=’’,NULL,OSVERSION),NETWORKTYPE=if(NETWORKTYPE=’’,NULL,NETWORKTYPE),APN=if(APN=’’,NULL,APN),LAC=if(LAC=’’,NULL,LAC),CI=if(CI=’’,NULL,CI),FUNC_NAME=if(FUNC_NAME=’’,NULL,FUNC_NAME),FUNC_FLAG=if(FUNC_FLAG=’’,NULL,FUNC_FLAG),REMARK=if(REMARK=’’,NULL,REMARK),RETCODE=if(RETCODE=’’,NULL,RETCODE),ERROR=if(ERROR=’’,NULL,ERROR),DURATION=if(DURATION=’’,NULL,DURATION),SERVERIP=if(SERVERIP=’’,NULL,SERVERIP),THREAD_NAME=if(THREAD_NAME=’’,NULL,THREAD_NAME),SESSION_ID=if(SESSION_ID=’’,NULL,SESSION_ID),功能ID=if(功能ID=’’,NULL,功能ID);
能把数据发一下么 还有表结构信息
CREATE TABLE tb_服务器性能监控_数据_原始log_url (
ID int(11) DEFAULT NULL,
Time datetime(3) NOT NULL,
userid int(11) DEFAULT NULL,
Login_Code varchar(50) NOT NULL,
IP varchar(50) NOT NULL,
URL varchar(256) NOT NULL,
Interface_Name varchar(255) DEFAULT NULL,
logType int(11) DEFAULT NULL,
imsi varchar(50) DEFAULT NULL,
imei varchar(50) DEFAULT NULL,
version varchar(50) DEFAULT NULL,
model varchar(50) DEFAULT NULL,
OSversion varchar(50) DEFAULT NULL,
networktype varchar(50) DEFAULT NULL,
apn varchar(50) DEFAULT NULL,
lac int(11) DEFAULT NULL,
ci int(11) DEFAULT NULL,
Func_Name varchar(50) DEFAULT NULL,
Func_Flag int(11) DEFAULT NULL,
Remark varchar(2048) DEFAULT NULL,
retcode int(11) DEFAULT NULL,
error varchar(255) DEFAULT NULL,
duration int(11) DEFAULT NULL,
SERVERIP varchar(255) DEFAULT NULL,
thread_name varchar(255) DEFAULT NULL,
session_id varchar(255) DEFAULT NULL,
功能ID int(11) DEFAULT NULL,
KEY idx_tb_服务器性能监控_数据_原始LOG_URL (Time) USING BTREE,
KEY idx2_tb_服务器性能监控_数据_原始LOG_URL (Login_Code) USING BTREE,
KEY idx3_tb_服务器性能监控_数据_原始LOG_URL (URL) USING BTREE
)
新用户无法上传文件，以下是文件内容。
2025-12-16 18:20:09.418$$tt_hrb_chenyuxuan$$-1$$10.151.89.160, 10.151.89.159,10.151.130.12$$/open/ZHJKGetSheetList$$智慧装维-待办工单列表$$-1$$46001B03AC9C4AA175$$86XIAOMI5CDF883041$$3.0.1$$M2012K11AC$$13$$WIFI$$$$-1$$-1$$智慧装维-待办工单列表$$$$$$1$$$$64$$127.0.0.1$$http-nio-8093-exec-4$$752C4EB243D5C59617F8E95D4F929BC9$$
2025-12-16 18:20:15.419$$tt_hrb_chenyuxuan$$-1$$10.151.89.160, 10.151.89.159,10.151.130.12$$/open/ZHJKGetSheetList$$智慧装维-待办工单列表$$-1$$46001B03AC9C4AA175$$86XIAOMI5CDF883041$$3.0.1$$M2012K11AC$$13$$WIFI$$$$-1$$-1$$智慧装维-待办工单列表$$$$$$1$$$$132$$127.0.0.1$$http-nio-8093-exec-5$$7F47FA8DF63CC00FB45355177371BBAC$$
2025-12-16 18:20:57.424$$tt_hrb_chenyuxuan$$-1$$10.151.89.160, 10.151.89.159,10.151.130.12$$/open/queryImportantCustomerInfo$$查询重点客户信息$$-1$$46001B03AC9C4AA175$$86XIAOMI5CDF883041$$3.0.1$$M2012K11AC$$13$$WIFI$$$$-1$$-1$$查询重点客户信息$$$$$$1$$$$18$$127.0.0.1$$http-nio-8093-exec-6$$11115F9C026ECD02011D88F193975306$$
尽量保存到文件里 太乱了数据 我看你的等级可以上传文件呀
文件可以上传了，已上传
你可以试一下 不处理null值 导入一次看看 重新弄一个文件 里面不包含null值 看着是表达式不支持
修改SQL，通过用户变量的方式可以对文件中的空值进行处理。测试中发现当用户变量命名为大写或包含中文，报错提示ERROR 5112 (42P01): Unknown user variable，不清楚是什么问题导致的。
正确SQL
LOAD DATA LOCAL INFILE '/data/mastercom/app/backend/logs/hlj-warehouse/interface/BAK_Interface_log_127.0.0.1_202512161817.log'
INTO TABLE `mtapp_mng_center`.`tb_服务器性能监控_数据_原始log_url_dd_251215`
FIELDS TERMINATED BY '$$'
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 0 LINES
(@col1, @col2, @col3, @col4, @col5, @col6, @col7, @col8, @col9, @col10,
@col11, @col12, @col13, @col14, @col15, @col16, @col17, @col18, @col19, @col20,
@col21, @col22, @col23, @col24, @col25, @col_26)
SET
`TIME` = NULLIF(@col1, ''),
`USERID` = NULLIF(@col2, ''),
`LOGIN_CODE` = NULLIF(@col3, ''),
`IP` = NULLIF(@col4, ''),
`URL` = NULLIF(@col5, ''),
`INTERFACE_NAME` = NULLIF(@col6, ''),
`LOGTYPE` = NULLIF(@col7, ''),
`IMSI` = NULLIF(@col8, ''),
`IMEI` = NULLIF(@col9, ''),
`VERSION` = NULLIF(@col10, ''),
`MODEL` = NULLIF(@col11, ''),
`OSVERSION` = NULLIF(@col12, ''),
`NETWORKTYPE` = NULLIF(@col13, ''),
`APN` = NULLIF(@col14, ''),
`LAC` = NULLIF(@col15, ''),
`CI` = NULLIF(@col16, ''),
`FUNC_NAME` = NULLIF(@col17, ''),
`FUNC_FLAG` = NULLIF(@col18, ''),
`REMARK` = NULLIF(@col19, ''),
`RETCODE` = NULLIF(@col20, ''),
`ERROR` = NULLIF(@col21, ''),
`DURATION` = NULLIF(@col22, ''),
`SERVERIP` = NULLIF(@col23, ''),
`THREAD_NAME` = NULLIF(@col24, ''),
`SESSION_ID` = NULLIF(@col25, ''),
`功能ID` = NULLIF(@col_26, '');
错误的SQL
当变量定义为@功能ID
`功能ID` = NULLIF(@功能ID, '');
LOAD DATA LOCAL INFILE '/data/mastercom/app/backend/logs/hlj-warehouse/interface/BAK_Interface_log_127.0.0.1_202512161817.log'
INTO TABLE `mtapp_mng_center`.`tb_服务器性能监控_数据_原始log_url_dd_251215`
FIELDS TERMINATED BY '$$'
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 0 LINES
(@col1, @col2, @col3, @col4, @col5, @col6, @col7, @col8, @col9, @col10,
@col11, @col12, @col13, @col14, @col15, @col16, @col17, @col18, @col19, @col20,
@col21, @col22, @col23, @col24, @col25, @功能ID)
SET
`TIME` = NULLIF(@col1, ''),
`USERID` = NULLIF(@col2, ''),
`LOGIN_CODE` = NULLIF(@col3, ''),
`IP` = NULLIF(@col4, ''),
`URL` = NULLIF(@col5, ''),
`INTERFACE_NAME` = NULLIF(@col6, ''),
`LOGTYPE` = NULLIF(@col7, ''),
`IMSI` = NULLIF(@col8, ''),
`IMEI` = NULLIF(@col9, ''),
`VERSION` = NULLIF(@col10, ''),
`MODEL` = NULLIF(@col11, ''),
`OSVERSION` = NULLIF(@col12, ''),
`NETWORKTYPE` = NULLIF(@col13, ''),
`APN` = NULLIF(@col14, ''),
`LAC` = NULLIF(@col15, ''),
`CI` = NULLIF(@col16, ''),
`FUNC_NAME` = NULLIF(@col17, ''),
`FUNC_FLAG` = NULLIF(@col18, ''),
`REMARK` = NULLIF(@col19, ''),
`RETCODE` = NULLIF(@col20, ''),
`ERROR` = NULLIF(@col21, ''),
`DURATION` = NULLIF(@col22, ''),
`SERVERIP` = NULLIF(@col23, ''),
`THREAD_NAME` = NULLIF(@col24, ''),
`SESSION_ID` = NULLIF(@col25, ''),
`功能ID` = NULLIF(@功能ID, '');
当变量定义为@COL26
`功能ID` = NULLIF(@COL26, '');
LOAD DATA LOCAL INFILE '/data/mastercom/app/backend/logs/hlj-warehouse/interface/BAK_Interface_log_127.0.0.1_202512161817.log'
INTO TABLE `mtapp_mng_center`.`tb_服务器性能监控_数据_原始log_url_dd_251215`
FIELDS TERMINATED BY '$$'
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 0 LINES
(@col1, @col2, @col3, @col4, @col5, @col6, @col7, @col8, @col9, @col10,
@col11, @col12, @col13, @col14, @col15, @col16, @col17, @col18, @col19, @col20,
@col21, @col22, @col23, @col24, @col25, @COL26)
SET
`TIME` = NULLIF(@col1, ''),
`USERID` = NULLIF(@col2, ''),
`LOGIN_CODE` = NULLIF(@col3, ''),
`IP` = NULLIF(@col4, ''),
`URL` = NULLIF(@col5, ''),
`INTERFACE_NAME` = NULLIF(@col6, ''),
`LOGTYPE` = NULLIF(@col7, ''),
`IMSI` = NULLIF(@col8, ''),
`IMEI` = NULLIF(@col9, ''),
`VERSION` = NULLIF(@col10, ''),
`MODEL` = NULLIF(@col11, ''),
`OSVERSION` = NULLIF(@col12, ''),
`NETWORKTYPE` = NULLIF(@col13, ''),
`APN` = NULLIF(@col14, ''),
`LAC` = NULLIF(@col15, ''),
`CI` = NULLIF(@col16, ''),
`FUNC_NAME` = NULLIF(@col17, ''),
`FUNC_FLAG` = NULLIF(@col18, ''),
`REMARK` = NULLIF(@col19, ''),
`RETCODE` = NULLIF(@col20, ''),
`ERROR` = NULLIF(@col21, ''),
`DURATION` = NULLIF(@col22, ''),
`SERVERIP` = NULLIF(@col23, ''),
`THREAD_NAME` = NULLIF(@col24, ''),
`SESSION_ID` = NULLIF(@col25, ''),
`功能ID` = NULLIF(@COL26, '');
意思是这样定义@功能ID变量 会报ERROR 5112 (42P01): Unknown user variable 这个错是么？
当变量定义为@功能ID
功能ID = NULLIF(@功能ID, ‘’);
是的变量定义为中文 或者是 字母大写的英文都会报这个错误
我找一下相关的同学 确定一下 看看是否符合预期
SHOW VARIABLES like ‘version_comment’;
ob的具体版本号
按照下面的步骤抓一下日志信息
alter system set enable_rich_error_msg=true;
obclient [test]> select count(*) from t2;
ERROR 1146 (42S02): Table ‘test.t2’ doesn’t exist
[xx.xx.xx.1:2882] [2024-04-13 20:10:20.292087] [YB420BA1CC68-000615A0A8EA5E38-0-0]
[root@x.x.x.1 ~]$ grep “YB420BA1CC68-000615A0A8EA5E38-0-0” rootservice.log
[root@x.x.x.1 ~]$ grep “YB420BA1CC68-000615A0A8EA5E38-0-0” observer.log
alter system set enable_rich_error_msg=false;
±----------------±-----------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
±----------------±-----------------------------------------------------------------------------------------------------------------+
| version_comment | OceanBase_CE 4.3.5.3 (r103000092025080818-e8da5f0afb288ed0add0613740c6ccf2a3c6830b) (Built Aug 8 2025 18:48:23) |
±----------------±-----------------------------------------------------------------------------------------------------------------+
内部有个大小写敏感匹配的问题，所以涉及到大写字母的变量会有匹配不上的问题，这个后续会修复掉。现在用小写字母没有问题，跟中文也没有关系。