数据库函数比较：修订间差异
跳到导航
跳到搜索
（创建页面，内容为“=== MySQL - Doris === {| class="wikitable" |Type |MySQL |Doris |Result |- |Json |select j1 -> '$."v1"' from ( select cast('{"v1":"10", "v2":"11"}' as json) j1 ) o <nowiki>;</nowiki> | |"10" |- |Json |select JSON_EXTRACT(cast('{"v1":"10", "v2":"11"}' as json), '$."v1"') |= |"10" |- |String |select trim('"10"', '"') |= |10 |- | | | | |}”） |
（→Json） |
||
| （未显示同一用户的4个中间版本） | |||
| 第1行： | 第1行： | ||
=== | === Date === | ||
{| class="wikitable" | {| class="wikitable" | ||
| | | | ||
|MySQL | |MySQL | ||
| | |Clickhouse | ||
|Oracle | |||
|Result | |Result | ||
|- | |- | ||
| | |转字符串 | ||
| | |date_format(now(), '%Y%m%d%H%m%S') | ||
|toYYYYMMDDhhmmss(now()) | |||
|to_char(sysdate, 'yyyymmddhh24miss') | |||
|20231129103552 | |||
| | |||
|- | |- | ||
| | |日期增 | ||
| | |adddate(now(), 10) | ||
| | |date_add(day, 10, now()) | ||
| | |sysdate + 10 | ||
|2023/11/19 10:18:10 | |||
|- | |- | ||
| | |日期减 | ||
| | |subdate(now(), 10) | ||
| | |date_sub(day, 10, now()) | ||
|10 | |sysdate - 10 | ||
|2023/12/9 10:16:22 | |||
|- | |- | ||
|默认日期 | |||
|default current_timestamp | |||
|default now() | |||
|default sysdate | |||
|2023/11/19 10:22:15 | |||
|} | |||
=== Rand === | |||
{| class="wikitable" | |||
| | | | ||
|MySQL | |||
|Clickhouse | |||
|Oracle | |||
|Result | |||
|- | |||
|随机数 | |||
|cast(rand() * 1000 as int) | |||
|trunc(rand()/10000000) | |||
|dbms_random.value(0,1000) | |||
|121 | |||
|} | |||
=== Json === | |||
{| class="wikitable" | |||
| | | | ||
| | |MySQL | ||
| | |Doris | ||
|Clickhouse | |||
|Result | |||
|- | |||
|字符串转为Json | |||
|select cast('{"v1":"10", "v2":"11"}' as json) | |||
|select cast('{"v1":"10", "v2":"11"}' as json) | |||
|Auto | |||
|{"v1":"10", "v2":"11"} | |||
|- | |||
|取Json字段值 | |||
|select j1 ->> '$."v1"' | |||
|select get_json_string(j1, '$.v1') | |||
|select JSONExtractString(j1, 'v1') | |||
|10 | |||
|} | |} | ||
[[分类:Develop]] | |||
[[分类:DB]] | |||
2023年11月30日 (四) 12:02的最新版本
Date
| MySQL | Clickhouse | Oracle | Result | |
| 转字符串 | date_format(now(), '%Y%m%d%H%m%S') | toYYYYMMDDhhmmss(now()) | to_char(sysdate, 'yyyymmddhh24miss') | 20231129103552 |
| 日期增 | adddate(now(), 10) | date_add(day, 10, now()) | sysdate + 10 | 2023/11/19 10:18:10 |
| 日期减 | subdate(now(), 10) | date_sub(day, 10, now()) | sysdate - 10 | 2023/12/9 10:16:22 |
| 默认日期 | default current_timestamp | default now() | default sysdate | 2023/11/19 10:22:15 |
Rand
| MySQL | Clickhouse | Oracle | Result | |
| 随机数 | cast(rand() * 1000 as int) | trunc(rand()/10000000) | dbms_random.value(0,1000) | 121 |
Json
| MySQL | Doris | Clickhouse | Result | |
| 字符串转为Json | select cast('{"v1":"10", "v2":"11"}' as json) | select cast('{"v1":"10", "v2":"11"}' as json) | Auto | {"v1":"10", "v2":"11"} |
| 取Json字段值 | select j1 ->> '$."v1"' | select get_json_string(j1, '$.v1') | select JSONExtractString(j1, 'v1') | 10 |