-
Notifications
You must be signed in to change notification settings - Fork 3
Description
ClickHouse不支持REGEXP关键字,ClickHouse默认区分大小写
原SQL语句
WHERE ( ( ( (User.name REGEXP BINARY 'a' OR User.name REGEXP BINARY 't') ) ) ) LIMIT 10修改为:
WHERE ( (match(`User`.`name`,'a') OR match(`User`.`name`,'t') ) ) LIMIT 10如果不区分大小写,可以修改为
WHERE ( ( ( (match(lower(`User`.`name`),lower('a')) OR match(lower(`User`.`name`),lower('t')) ) ) ) LIMIT 10目前的简单测试方式:
在DemoSQLconfig里重写getRegExpString函数:
//由于无法判断是否为ClickHouse数据库,所以直接忽略了其他数据库
public String getRegExpString(String key, String value, boolean ignoreCase) {
//实现getValue私有方法的逻辑
String getValue="";
if (this.isPrepared()) {
this.getPreparedValueList().add(value);
getValue = "?";
} else {
getValue = (String)this.getSQLValue(value);
}
return "match(" + (ignoreCase ? "lower(" : "") + this.getKey(key) + (ignoreCase ? ")" : "") + ", " + (ignoreCase ? "lower(" : "") + getValue + (ignoreCase ? ")" : "") + ")";
}例子
修改前
请求:
{
"[]": {
"join": "&/User/id@",
"Moment": {
"@column": "id,userId,content"
},
"User": {
"name~": [
"a",
"t"
],
"@column": "id,name",
"id@": "/Moment/userId"
}
}
}生成SQL:
SELECT Moment.id,Moment.userId,Moment.content, User.id,User.name FROM sys.Moment AS Moment INNER JOIN sys.apijson_user AS User ON User.id = Moment.userId WHERE ( ( ( (User.name REGEXP BINARY 'a' OR User.name REGEXP BINARY 't') ) ) ) LIMIT 10ClickHouse执行出错:
Syntax error: failed at position 229 ('REGEXP') (line 2, col 112):
SELECT Moment.id,Moment.userId,Moment.content, User.id,User.name FROM sys.Moment AS Moment
INNER JOIN sys.apijson_user AS User ON User.id = Moment.userId WHERE ( ( ( (User.name REGEXP BINARY 'a' OR User.name REGEXP BINARY 't') ) ) ) LIMIT 10
Expected one of: DoubleColon, LIKE, GLOBAL NOT IN, AS, DIV, IS, UUID, OR, QuestionMark, BETWEEN, NOT LIKE, MOD, AND, Comma, alias, IN, ILIKE, Dot, NOT ILIKE, NOT, token, NOT IN, GLOBAL IN
修改后
请求:
{
"[]": {
"join": "&/User/id@",
"Moment": {
"@column": "id,userId,content"
},
"User": {
"name~": [
"a",
"t"
],
"@column": "id,name",
"id@": "/Moment/userId"
}
}
}结果:
{ "[]": [ { "Moment": { "id": 12 , "userId": 70793 , "content": "APIJSON,let interfaces and documents go to hell !" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 15 , "userId": 70793 , "content": "APIJSON is a JSON Transmission Structure Protocol…" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 32 , "userId": 82002 , "content": "tst" }, "User": { "id": 82002 , "name": "Happy~" } }, { "Moment": { "id": 170 , "userId": 70793 , "content": "This is a Content...-73" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 371 , "userId": 82002 , "content": "This is a Content...-371" }, "User": { "id": 82002 , "name": "Happy~" } }, { "Moment": { "id": 551 , "userId": 70793 , "content": "test" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 1508053762227 , "userId": 82003 , "content": "我也试试" }, "User": { "id": 82003 , "name": "Wechat" } }, { "Moment": { "id": 1508072491570 , "userId": 82002 , "content": "有点冷~" }, "User": { "id": 82002 , "name": "Happy~" } }, { "Moment": { "id": 1514017444961 , "userId": 82002 , "content": "123479589679" }, "User": { "id": 82002 , "name": "Happy~" } }, { "Moment": { "id": 1514858533480 , "userId": 82056 , "content": "I am the Iron Man" }, "User": { "id": 82056 , "name": "IronMan" } } ], "ok": true , "msg": "success" , "code": 200 }
请求(忽略大小写):
{
"[]": {
"join": "&/User/id@",
"Moment": {
"@column": "id,userId,content"
},
"User": {
"name*~": [
"a",
"t"
],
"@column": "id,name",
"id@": "/Moment/userId"
}
}
}结果:
{ "[]": [ { "Moment": { "id": 12 , "userId": 70793 , "content": "APIJSON,let interfaces and documents go to hell !" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 15 , "userId": 70793 , "content": "APIJSON is a JSON Transmission Structure Protocol…" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 32 , "userId": 82002 , "content": "tst" }, "User": { "id": 82002 , "name": "Happy~" } }, { "Moment": { "id": 170 , "userId": 70793 , "content": "This is a Content...-73" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 235 , "userId": 38710 , "content": "APIJSON,let interfaces and documents go to hell !" }, "User": { "id": 38710 , "name": "TommyLemon" } }, { "Moment": { "id": 371 , "userId": 82002 , "content": "This is a Content...-371" }, "User": { "id": 82002 , "name": "Happy~" } }, { "Moment": { "id": 470 , "userId": 38710 , "content": "This is a Content...-470" }, "User": { "id": 38710 , "name": "TommyLemon" } }, { "Moment": { "id": 511 , "userId": 38710 }, "User": { "id": 38710 , "name": "TommyLemon" } }, { "Moment": { "id": 551 , "userId": 70793 , "content": "test" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 595 , "userId": 38710 }, "User": { "id": 38710 , "name": "TommyLemon" } } ], "ok": true , "code": 200 , "msg": "success" , "sql:generate|cache|execute|maxExecute": "11|7|4|200" , "depth:count|max": "3|5" , "time:start|duration|end": "1627478385147|115|1627478385262" }