解決Mysql同步到ES時(shí)date和time字段類型轉(zhuǎn)換問(wèn)題
這個(gè)問(wèn)題中發(fā)生問(wèn)題的es字段映射類型為date類型！??！
字符串類型的會(huì)好很多，關(guān)鍵我這邊是通過(guò)logstash生成索引，并且全量同步的，不好改索引類型?。?！
問(wèn)題描述：
這個(gè)報(bào)錯(cuò)我該怎么辦呢2023-07-10 03:01:39.104 [pool-2-thread-1] INFO c.a.o.canal.client.adapter.logger.LoggerAdapterExample - DML: {"data":[{"ORDER_ID":"4444","SETTLE_DATE_LOC":"20200329","TRANS_DATE":"20200329","TRANS_TIME":"165440","USER_ID":null,"SPACE_NO":null,"SPACE_NAME":null,"PARK_NO":"JC01331","PARK_NAME":"DELETED","CARNO":"滬A666666","CAR_IN_SEQ":"20200329165438071000000000785843","CAR_OUT_SEQ":null,"START_TIME":"20181126103000","END_TIME":null,"LENGTH":null,"OVER_VALID_TIME":null,"LEAVE_TIME":null,"STATUS":"03","PAY_STYLE":null,"TRANS_AMT":"0","PRE_AMT":"0","BALANCE":"0","FAV_AMT":"0","RET_AMT":"0","RET_STATUS":null,"SETTLE_STATUS":null,"SETTLE_DATE":null,"SETTLE_SEQ":null,"SETTLE_AMT":null,"REC_CRT_TIME":"20200329165440","REC_UPD_TIME":"20200329165440","OPR_FLAG":"0","RESVD1":null,"RESVD2":"11111111111111111111111111111111","IF_HOLD":"0","LAST_UPD_TIME":null,"LAST_UPD_RESV":null,"RESVD3":null,"RESVD4":null,"RESVD5":null,"RESVD6":null,"IN_TYPE":"00","IF_RENT":null,"ORG_ID":"0","MCHNT_NO":"1000058","MCHNT_NAME":"捷順總部大廈停車場(chǎng)","IN_OUT_DATE":"2018-11-26","LEAVE_DATE":null,"IF_SCORE":"0","EXCEP_TYPE":null,"EXCEP_DATE":null,"EXCEP_TIME":null,"EXCEP_STATUS":null,"IF_PAYBACK":null,"DEVICE_TYPE":"112","SUB_DEVICE_TYPE":"r"}],"database":"cstc245_test","destination":"example","es":1688929250000,"groupId":"g1","isDdl":false,"old":null,"pkNames":["ORDER_ID","IN_OUT_DATE"],"sql":"","table":"tbl_ps_order_in","ts":1688929299104,"type":"INSERT"}
2023-07-10 03:01:39.107 [pool-2-thread-1] DEBUG c.a.o.canal.client.adapter.es.core.service.ESSyncService - DML: {"data":[{"ORDER_ID":"4444","SETTLE_DATE_LOC":"20200329","TRANS_DATE":"20200329","TRANS_TIME":"165440","USER_ID":null,"SPACE_NO":null,"SPACE_NAME":null,"PARK_NO":"JC01331","PARK_NAME":"DELETED","CARNO":"滬A666666","CAR_IN_SEQ":"20200329165438071000000000785843","CAR_OUT_SEQ":null,"START_TIME":"20181126103000","END_TIME":null,"LENGTH":null,"OVER_VALID_TIME":null,"LEAVE_TIME":null,"STATUS":"03","PAY_STYLE":null,"TRANS_AMT":"0","PRE_AMT":"0","BALANCE":"0","FAV_AMT":"0","RET_AMT":"0","RET_STATUS":null,"SETTLE_STATUS":null,"SETTLE_DATE":null,"SETTLE_SEQ":null,"SETTLE_AMT":null,"REC_CRT_TIME":"20200329165440","REC_UPD_TIME":"20200329165440","OPR_FLAG":"0","RESVD1":null,"RESVD2":"11111111111111111111111111111111","IF_HOLD":"0","LAST_UPD_TIME":null,"LAST_UPD_RESV":null,"RESVD3":null,"RESVD4":null,"RESVD5":null,"RESVD6":null,"IN_TYPE":"00","IF_RENT":null,"ORG_ID":"0","MCHNT_NO":"1000058","MCHNT_NAME":"捷順總部大廈停車場(chǎng)","IN_OUT_DATE":"2018-11-26","LEAVE_DATE":null,"IF_SCORE":"0","EXCEP_TYPE":null,"EXCEP_DATE":null,"EXCEP_TIME":null,"EXCEP_STATUS":null,"IF_PAYBACK":null,"DEVICE_TYPE":"112","SUB_DEVICE_TYPE":"r"}],"database":"cstc245_test","destination":"example","es":1688929250000,"groupId":"g1","isDdl":false,"old":null,"pkNames":["ORDER_ID","IN_OUT_DATE"],"sql":"","table":"tbl_ps_order_in","ts":1688929299104,"type":"INSERT"}
Affected indexes: tbl_ps_order_in
2023-07-10 03:01:39.116 [pool-2-thread-1] ERROR c.a.otter.canal.adapter.launcher.loader.AdapterProcessor - ES sync commit error ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse field [START_TIME] of type [date] in document with id '4444'. Preview of field's value: '2018-11-26 10:30:00']]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=failed to parse date field [2018-11-26 10:30:00] with format [strict_date_optional_time||epoch_millis]]]; nested: ElasticsearchException[Elasticsearch exception [type=date_time_parse_exception, reason=Failed to parse with all enclosed parsers]];
java.lang.RuntimeException: ES sync commit error ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse field [START_TIME] of type [date] in document with id '4444'. Preview of field's value: '2018-11-26 10:30:00']]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=failed to parse date field [2018-11-26 10:30:00] with format [strict_date_optional_time||epoch_millis]]]; nested: ElasticsearchException[Elasticsearch exception [type=date_time_parse_exception, reason=Failed to parse with all enclosed parsers]];
at com.alibaba.otter.canal.client.adapter.es7x.support.ESConnection$ES7xBulkResponse.processFailBulkResponse(ESConnection.java:470) ~[na:na]
at com.alibaba.otter.canal.client.adapter.es7x.support.ES7xTemplate.commit(ES7xTemplate.java:175) ~[na:na]
at com.alibaba.otter.canal.client.adapter.es.core.service.ESSyncService.commit(ESSyncService.java:873) ~[na:na]
at com.alibaba.otter.canal.client.adapter.es.core.ESAdapter.sync(ESAdapter.java:86) ~[na:na]
at com.alibaba.otter.canal.client.adapter.ProxyOuterAdapter.sync(ProxyOuterAdapter.java:42) ~[client-adapter.common-1.1.7-SNAPSHOT.jar:na]
at com.alibaba.otter.canal.adapter.launcher.loader.AdapterProcessor.batchSync(AdapterProcessor.java:139) ~[client-adapter.launcher-1.1.7-SNAPSHOT.jar:na]
at com.alibaba.otter.canal.adapter.launcher.loader.AdapterProcessor.lambda$null$1(AdapterProcessor.java:97) ~[client-adapter.launcher-1.1.7-SNAPSHOT.jar:na]
at java.util.concurrent.CopyOnWriteArrayList.forEach(CopyOnWriteArrayList.java:895) ~[na:1.8.0_262]
at com.alibaba.otter.canal.adapter.launcher.loader.AdapterProcessor.lambda$null$2(AdapterProcessor.java:94) ~[client-adapter.launcher-1.1.7-SNAPSHOT.jar:na]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[na:1.8.0_262]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_262]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_262]
at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_262]
2023-07-10 03:01:39.116 [Thread-4] ERROR c.a.otter.canal.adapter.launcher.loader.AdapterProcessor - Outer adapter sync failed! Error sync and rollback, execute times: 17
^C
根據(jù)錯(cuò)誤日志，似乎是Elasticsearch無(wú)法解析日期字段START_TIME。這個(gè)字段的格式是YYYY-MM-DD HH:MM:SS，而Elasticsearch期望的格式是strict_date_optional_time||epoch_millis。
strict_date_optional_time格式的例子是2015-01-01T12:10:30Z，而epoch_millis則是自1970-01-01 00:00:00 UTC以來(lái)的毫秒數(shù)。錯(cuò)誤提示的意思是Elasticsearch試圖使用這兩種格式解析START_TIME字段，但是都失敗了。
從這個(gè)錯(cuò)誤信息來(lái)看，你可能需要將MySQL中的日期時(shí)間數(shù)據(jù)轉(zhuǎn)換為Elasticsearch可以接受的格式。你可以嘗試將日期時(shí)間數(shù)據(jù)轉(zhuǎn)換為ISO 8601格式，或者轉(zhuǎn)換為epoch_millis格式。
如果你有權(quán)限更改Elasticsearch索引的映射，你也可以更改START_TIME字段的類型為text，這樣Elasticsearch就會(huì)將其當(dāng)作普通字符串處理，而不會(huì)嘗試將其解析為日期。
這種情況下，你可能需要更改日期字段的處理方式，你可以在SQL查詢中使用UNIX_TIMESTAMP()函數(shù)將日期時(shí)間轉(zhuǎn)換為epoch格式，或者在數(shù)據(jù)同步到Elasticsearch之前進(jìn)行格式轉(zhuǎn)換。具體解決方案需要根據(jù)你的實(shí)際情況來(lái)確定。
提問(wèn)： 試了沒(méi)用?。?！
將包含 'date' 的字段使用 DATE_FORMAT 轉(zhuǎn)換，只需要年月日就行，包含time的字段需要年月日時(shí)分秒并且嘗試將日期時(shí)間數(shù)據(jù)轉(zhuǎn)換為ISO 8601格式
SELECT
a.ORDER_ID as _id,
a.ORDER_ID,
a.SETTLE_DATE_LOC,
a.TRANS_DATE,
a.TRANS_TIME,
a.USER_ID,
a.SPACE_NO,
a.SPACE_NAME,
a.PARK_NO,
a.PARK_NAME,
a.CARNO,
a.CAR_IN_SEQ,
a.CAR_OUT_SEQ,
a.START_TIME,
a.END_TIME,
a.LENGTH,
a.OVER_VALID_TIME,
a.LEAVE_TIME,
a.STATUS,
a.PAY_STYLE,
a.TRANS_AMT,
a.PRE_AMT,
a.BALANCE,
a.FAV_AMT,
a.RET_AMT,
a.RET_STATUS,
a.SETTLE_STATUS,
a.SETTLE_DATE,
a.SETTLE_SEQ,
a.SETTLE_AMT,
a.REC_CRT_TIME,
a.REC_UPD_TIME,
a.OPR_FLAG,
a.RESVD1,
a.RESVD2,
a.IF_HOLD,
a.LAST_UPD_TIME,
a.LAST_UPD_RESV,
a.RESVD3,
a.RESVD4,
a.RESVD5,
a.RESVD6,
a.IN_TYPE,
a.IF_RENT,
a.ORG_ID,
a.MCHNT_NO,
a.MCHNT_NAME,
a.IN_OUT_DATE,
a.LEAVE_DATE,
a.IF_SCORE,
a.EXCEP_TYPE,
a.EXCEP_DATE,
a.EXCEP_TIME,
a.EXCEP_STATUS,
a.IF_PAYBACK,
a.DEVICE_TYPE,
a.SUB_DEVICE_TYPE
FROM tbl_ps_order_in a回答
你想將查詢中包含 ‘date’ 的字段用 DATE_FORMAT 轉(zhuǎn)換為僅包含年月日的格式，同時(shí)，包含 ‘time’ 的字段需要年月日時(shí)分秒，而且要將日期時(shí)間數(shù)據(jù)轉(zhuǎn)換為ISO 8601格式。以下是你可以使用的SQL語(yǔ)句：
SELECT
a.ORDER_ID as _id,
a.ORDER_ID,
DATE_FORMAT(a.SETTLE_DATE_LOC, '%Y-%m-%d') as SETTLE_DATE_LOC,
DATE_FORMAT(a.TRANS_DATE, '%Y-%m-%d') as TRANS_DATE,
DATE_FORMAT(a.TRANS_TIME, '%Y-%m-%dT%H:%i:%s') as TRANS_TIME,
a.USER_ID,
a.SPACE_NO,
a.SPACE_NAME,
a.PARK_NO,
a.PARK_NAME,
a.CARNO,
a.CAR_IN_SEQ,
a.CAR_OUT_SEQ,
DATE_FORMAT(a.START_TIME, '%Y-%m-%dT%H:%i:%s') as START_TIME,
DATE_FORMAT(a.END_TIME, '%Y-%m-%dT%H:%i:%s') as END_TIME,
a.LENGTH,
a.OVER_VALID_TIME,
DATE_FORMAT(a.LEAVE_TIME, '%Y-%m-%dT%H:%i:%s') as LEAVE_TIME,
a.STATUS,
a.PAY_STYLE,
a.TRANS_AMT,
a.PRE_AMT,
a.BALANCE,
a.FAV_AMT,
a.RET_AMT,
a.RET_STATUS,
a.SETTLE_STATUS,
DATE_FORMAT(a.SETTLE_DATE, '%Y-%m-%d') as SETTLE_DATE,
a.SETTLE_SEQ,
a.SETTLE_AMT,
DATE_FORMAT(a.REC_CRT_TIME, '%Y-%m-%dT%H:%i:%s') as REC_CRT_TIME,
DATE_FORMAT(a.REC_UPD_TIME, '%Y-%m-%dT%H:%i:%s') as REC_UPD_TIME,
a.OPR_FLAG,
a.RESVD1,
a.RESVD2,
a.IF_HOLD,
DATE_FORMAT(a.LAST_UPD_TIME, '%Y-%m-%dT%H:%i:%s') as LAST_UPD_TIME,
a.LAST_UPD_RESV,
a.RESVD3,
a.RESVD4,
a.RESVD5,
a.RESVD6,
a.IN_TYPE,
a.IF_RENT,
a.ORG_ID,
a.MCHNT_NO,
a.MCHNT_NAME,
DATE_FORMAT(a.IN_OUT_DATE, '%Y-%m-%d') as IN_OUT_DATE,
DATE_FORMAT(a.LEAVE_DATE, '%Y-%m-%d') as LEAVE_DATE,
a.IF_SCORE,
a.EXCEP_TYPE,
DATE_FORMAT(a.EXCEP_DATE, '%Y-%m-%d') as EXCEP_DATE,
DATE_FORMAT(a.EXCEP_TIME, '%Y-%m-%dT%H:%i:%s') as EXCEP_TIME,
a.EXCEP_STATUS,
a.IF_PAYBACK,
a.DEVICE_TYPE,
a.SUB_DEVICE_TYPE
FROM tbl_ps_order_in a注意，這個(gè) SQL 查詢是針對(duì) MySQL 數(shù)據(jù)庫(kù)的，假設(shè)所有的時(shí)間字段都是以字符串形式存儲(chǔ)，如果字段的存儲(chǔ)形式不同，這個(gè)查詢可能需要做相應(yīng)的調(diào)整。此外，‘%Y-%m-%dT%H:%i:%s’ 是 ISO 8601 時(shí)間格式的 MySQL 版本，如果你的數(shù)據(jù)庫(kù)不支持這種格式，你可能需要在應(yīng)用層面進(jìn)行處理。
這個(gè)貌似解決不了?。。。?！
下面是重頭戲
你可以使用UNIX_TIMESTAMP()函數(shù)將日期和時(shí)間字段轉(zhuǎn)換為epoch格式。這個(gè)函數(shù)將日期和時(shí)間的值轉(zhuǎn)換為自1970-01-01 00:00:00 UTC以來(lái)的秒數(shù)。以下是你的SQL查詢的一部分，展示了如何進(jìn)行這種轉(zhuǎn)換：
SELECT
a.ORDER_ID as _id,
a.ORDER_ID,
UNIX_TIMESTAMP(a.SETTLE_DATE_LOC) as SETTLE_DATE_LOC,
UNIX_TIMESTAMP(a.TRANS_DATE) as TRANS_DATE,
UNIX_TIMESTAMP(STR_TO_DATE(a.TRANS_TIME, '%Y-%m-%d %H:%i:%s')) as TRANS_TIME,
a.USER_ID,
...
UNIX_TIMESTAMP(STR_TO_DATE(a.START_TIME, '%Y-%m-%d %H:%i:%s')) as START_TIME,
UNIX_TIMESTAMP(STR_TO_DATE(a.END_TIME, '%Y-%m-%d %H:%i:%s')) as END_TIME,
...
UNIX_TIMESTAMP(a.SETTLE_DATE) as SETTLE_DATE,
...
UNIX_TIMESTAMP(STR_TO_DATE(a.REC_CRT_TIME, '%Y-%m-%d %H:%i:%s')) as REC_CRT_TIME,
UNIX_TIMESTAMP(STR_TO_DATE(a.REC_UPD_TIME, '%Y-%m-%d %H:%i:%s')) as REC_UPD_TIME,
...
UNIX_TIMESTAMP(STR_TO_DATE(a.LAST_UPD_TIME, '%Y-%m-%d %H:%i:%s')) as LAST_UPD_TIME,
...
UNIX_TIMESTAMP(a.IN_OUT_DATE) as IN_OUT_DATE,
UNIX_TIMESTAMP(a.LEAVE_DATE) as LEAVE_DATE,
...
UNIX_TIMESTAMP(a.EXCEP_DATE) as EXCEP_DATE,
UNIX_TIMESTAMP(STR_TO_DATE(a.EXCEP_TIME, '%Y-%m-%d %H:%i:%s')) as EXCEP_TIME,
...
FROM tbl_ps_order_in a這里，我將所有的日期時(shí)間字段都用UNIX_TIMESTAMP()函數(shù)進(jìn)行了處理。對(duì)于時(shí)間字段（如TRANS_TIME、START_TIME等），我還使用了STR_TO_DATE()函數(shù)，將其從字符串格式轉(zhuǎn)換為日期時(shí)間格式，然后再調(diào)用UNIX_TIMESTAMP()。
注意，UNIX_TIMESTAMP()函數(shù)返回的是秒數(shù)，而Elasticsearch的epoch_millis格式是毫秒數(shù)。如果你需要毫秒數(shù)，你可以將UNIX_TIMESTAMP()函數(shù)的結(jié)果乘以1000。
以上的方案適用于MySQL。如果你的數(shù)據(jù)庫(kù)是其他類型，可能需要用不同的方法來(lái)進(jìn)行轉(zhuǎn)換。
**提問(wèn)：**試了有用?。。。?/p>
SELECT
a.ORDER_ID as _id,
a.ORDER_ID,
a.SETTLE_DATE_LOC,
a.TRANS_DATE,
a.TRANS_TIME,
a.USER_ID,
a.SPACE_NO,
a.SPACE_NAME,
a.PARK_NO,
a.PARK_NAME,
a.CARNO,
a.CAR_IN_SEQ,
a.CAR_OUT_SEQ,
a.START_TIME,
a.END_TIME,
a.LENGTH,
a.OVER_VALID_TIME,
a.LEAVE_TIME,
a.STATUS,
a.PAY_STYLE,
a.TRANS_AMT,
a.PRE_AMT,
a.BALANCE,
a.FAV_AMT,
a.RET_AMT,
a.RET_STATUS,
a.SETTLE_STATUS,
a.SETTLE_DATE,
a.SETTLE_SEQ,
a.SETTLE_AMT,
a.REC_CRT_TIME,
a.REC_UPD_TIME,
a.OPR_FLAG,
a.RESVD1,
a.RESVD2,
a.IF_HOLD,
a.LAST_UPD_TIME,
a.LAST_UPD_RESV,
a.RESVD3,
a.RESVD4,
a.RESVD5,
a.RESVD6,
a.IN_TYPE,
a.IF_RENT,
a.ORG_ID,
a.MCHNT_NO,
a.MCHNT_NAME,
a.IN_OUT_DATE,
a.LEAVE_DATE,
a.IF_SCORE,
a.EXCEP_TYPE,
a.EXCEP_DATE,
a.EXCEP_TIME,
a.EXCEP_STATUS,
a.IF_PAYBACK,
a.DEVICE_TYPE,
a.SUB_DEVICE_TYPE
FROM tbl_ps_order_in a將包含 'date' 的字段使用 DATE_FORMAT 轉(zhuǎn)換，只需要年月日就行，包含time的字段需要格式化成年月日時(shí)分秒，并且用UNIX_TIMESTAMP()函數(shù)進(jìn)行處理，然后還要將UNIX_TIMESTAMP()函數(shù)的結(jié)果乘以1000回答
根據(jù)您的需求，我將對(duì)包含 ‘date’ 的字段使用 DATE_FORMAT 進(jìn)行年月日的格式化，對(duì)包含 ‘time’ 的字段，先將其格式化為年月日時(shí)分秒，然后使用 UNIX_TIMESTAMP() 函數(shù)處理，最后將結(jié)果乘以1000。以下是更新后的 SQL 查詢：
SELECT
a.ORDER_ID as _id,
a.ORDER_ID,
DATE_FORMAT(a.SETTLE_DATE_LOC, '%Y-%m-%d') as SETTLE_DATE_LOC,
DATE_FORMAT(a.TRANS_DATE, '%Y-%m-%d') as TRANS_DATE,
UNIX_TIMESTAMP(DATE_FORMAT(a.TRANS_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as TRANS_TIME,
a.USER_ID,
a.SPACE_NO,
a.SPACE_NAME,
a.PARK_NO,
a.PARK_NAME,
a.CARNO,
a.CAR_IN_SEQ,
a.CAR_OUT_SEQ,
UNIX_TIMESTAMP(DATE_FORMAT(a.START_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as START_TIME,
UNIX_TIMESTAMP(DATE_FORMAT(a.END_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as END_TIME,
a.LENGTH,
a.OVER_VALID_TIME,
UNIX_TIMESTAMP(DATE_FORMAT(a.LEAVE_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as LEAVE_TIME,
a.STATUS,
a.PAY_STYLE,
a.TRANS_AMT,
a.PRE_AMT,
a.BALANCE,
a.FAV_AMT,
a.RET_AMT,
a.RET_STATUS,
a.SETTLE_STATUS,
DATE_FORMAT(a.SETTLE_DATE, '%Y-%m-%d') as SETTLE_DATE,
a.SETTLE_SEQ,
a.SETTLE_AMT,
UNIX_TIMESTAMP(DATE_FORMAT(a.REC_CRT_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as REC_CRT_TIME,
UNIX_TIMESTAMP(DATE_FORMAT(a.REC_UPD_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as REC_UPD_TIME,
a.OPR_FLAG,
a.RESVD1,
a.RESVD2,
a.IF_HOLD,
UNIX_TIMESTAMP(DATE_FORMAT(a.LAST_UPD_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as LAST_UPD_TIME,
a.LAST_UPD_RESV,
a.RESVD3,
a.RESVD4,
a.RESVD5,
a.RESVD6,
a.IN_TYPE,
a.IF_RENT,
a.ORG_ID,
a.MCHNT_NO,
a.MCHNT_NAME,
DATE_FORMAT(a.IN_OUT_DATE, '%Y-%m-%d') as IN_OUT_DATE,
DATE_FORMAT(a.LEAVE_DATE, '%Y-%m-%d') as LEAVE_DATE,
a.IF_SCORE,
a.EXCEP_TYPE,
DATE_FORMAT(a.EXCEP_DATE, '%Y-%m-%d') as EXCEP_DATE,
UNIX_TIMESTAMP(DATE_FORMAT(a.EXCEP_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as EXCEP_TIME,
a.EXCEP_STATUS,
a.IF_PAYBACK,
a.DEVICE_TYPE,
a.SUB_DEVICE_TYPE
FROM tbl_ps_order_in a注意：上述查詢是在假設(shè)所有包含 ‘time’ 的字段都是 TIMESTAMP 類型，可以被轉(zhuǎn)換為 ‘%Y-%m-%d %H:%i:%s’ 這種格式。如果不是這種類型，可能需要進(jìn)行一些調(diào)整。
到此這篇關(guān)于Mysql同步到ES時(shí)date和time字段類型轉(zhuǎn)換問(wèn)題解決辦法的文章就介紹到這了,更多相關(guān)mysql同步到ES內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL存儲(chǔ)過(guò)程及語(yǔ)法詳解，存儲(chǔ)過(guò)程，也叫做存儲(chǔ)程序，是一條或者多條SQL語(yǔ)句的集合，可以視為批量處理，但是其作用不僅僅局限于批量處理2022-08-08
MySQL中字段類型為longtext的值導(dǎo)出后顯示二進(jìn)制串方式
這篇文章主要介紹了MySQL中字段類型為longtext的值導(dǎo)出后顯示二進(jìn)制串方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-07-07
mysql使用SQLyog導(dǎo)入csv數(shù)據(jù)不成功的解決方法
給mysql導(dǎo)入數(shù)據(jù)，選中某個(gè)表選擇導(dǎo)入--導(dǎo)入使用本地csv數(shù)據(jù)即可，單有的時(shí)候不知道什么問(wèn)題導(dǎo)入不成功2014-07-07
這篇文章主要介紹了mysql中的int(5)到底有是多長(zhǎng)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-04-04
MySQL 是可以自定義臨時(shí)變量的，而臨時(shí)變量在做查詢優(yōu)化和靜態(tài)查詢分析時(shí)十分有用。而實(shí)際上很少有人記得 MySQL 的自定義變量。本篇文章講述 MySQL 的自定義變量的概念及特點(diǎn)。2021-05-05
一條sql詳解MYSQL的架構(gòu)設(shè)計(jì)詳情
這篇文章主要介紹了一條sql詳解MYSQL的架構(gòu)設(shè)計(jì)詳情，文章圍繞主題展開(kāi)詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，感興趣的小伙伴可以參考一下2022-09-09
mysql?使用join進(jìn)行多表關(guān)聯(lián)查詢的操作方法
在一些報(bào)表統(tǒng)計(jì)或數(shù)據(jù)展示時(shí)候需要提取的數(shù)據(jù)分布在多個(gè)表中,這個(gè)時(shí)候需要進(jìn)行join連表操作,join將兩個(gè)或多個(gè)表當(dāng)成不同的數(shù)據(jù)集合,然后進(jìn)行集合取交集運(yùn)算,這篇文章主要介紹了mysql?使用join進(jìn)行多表關(guān)聯(lián)查詢的操作方法,需要的朋友可以參考下2024-02-02
MySQL單表記錄數(shù)過(guò)大的優(yōu)化方法
當(dāng)MySQL單表記錄數(shù)過(guò)大時(shí),采取合理的優(yōu)化策略是保障系統(tǒng)高性能的關(guān)鍵,本博客詳細(xì)介紹了索引優(yōu)化、分區(qū)表、垂直拆分、水平拆分等多種優(yōu)化手段,并提供了詳細(xì)的代碼示例,感興趣的朋友一起看看吧2024-01-01
mysql建表常用sql語(yǔ)句個(gè)人經(jīng)驗(yàn)分享
熟悉一些常用的建表語(yǔ)句可以提升你建表的速度效率，本文整理了一些，個(gè)人感覺(jué)還不錯(cuò)，希望對(duì)大家有所幫助2014-01-01
MySQL 數(shù)據(jù)庫(kù)鎖的實(shí)現(xiàn)
本文主要介紹了MySQL 數(shù)據(jù)庫(kù)鎖的實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-03-03