CSV style LONGTEXT string into table splitted by comma TO multiple rows (PERFORMANCE)
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 56 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.