CSV style LONGTEXT string into table splitted by comma TO multiple rows (PERFORMANCE)
You are viewing an old version of this question. View
the current version here.
I have a LONGTEXT string as "A20210917_0701000,B20210917_0701033,A20210917_0703067, ......" (CSV style)
The data is in one row (One column). And it contains elements upto 350,000
In above case, like this ...
Element #1 = A20210917_0701000 Element #2 = B20210917_0701033 ... Element #350,000 = A20210917_2232072
BUT... problem is...
SET @ABC = (SELECT MyLongColumn FROM MyTable ORDER BY MyCond LIMIT 1); -- THIS IS NO PROBLEM, FAST LOADED.
But, this LONGTEXT data makes all SQL works TOO SLOW.
SELECT LENGTH(@ABC) - LENGTH(REPLACE(@ABC, ',', '')); -- THIS SIMPLE SQL COMMAND TOOKS 90 SECONDS. SELECT REPLACE(@ABC, ',', '::'); -- THIS SIMPLE SQL COMMAND TOOKS ABOUT 5~6 MINUTE SELECT REPLACE(@ABC, ',', ':'); -- THIS EXECUTED FAST !! (Under 100ms)
I WANT ALL OF THIS ELEMENTS ARE SAVED AS TABLE. LIKE THIS.
VALUE A20210917_0701000 (Row #1) B20210917_0701033 (Row #2) ... A20210917_2232072 (Row #350000)
I'm using MariaDB 10.3.11.
Is there any solution? (performance)
Answer
I'd save this to a file, SELECT .. INTO OUTFILE, or otherwise.
And reimport it with:
LOAD DATA INFILE specifying the comma as the "LINES TERMINATE BY" character (an set "FIELDS SEPARATE BY" to something else.
There's really not performant way to do this in SQL, especially a 10.3 version.
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.