Summary: My question is whether there is a better way to do bulk data updates through a stored procedure in MySQL or if my current approach is really the only thing I can do in my case?
I mainly develop in Microsoft SQL but one of my side projects uses MySQL and I have come to a point where I need to pass in a dynamic list of details and keys to a procedure so I can do a bulk update instead of calling a stored procedure multiple times for each key/detail pair.
In MSSQL, I can do something like this:
insert into @tbl(key, amount) select Tab.Col.value('key[1]', 'int'), Tab.Col.value('@count', 'int') from @xml.nodes('/Root/orderHRD') as Tab(Col)
But in MySQL, the only way I have been able to do the same thing is this:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(extractvalue(items, '/key'), ' ', num.num), ' ', -1), SUBSTRING_INDEX(SUBSTRING_INDEX(extractvalue(items, '/key/@count'), ' ', num.num), ' ', -1) FROM num where CHAR_LENGTH(extractvalue(items, '/key')) - CHAR_LENGTH(REPLACE(extractvalue(items, '/key'), ' ', ''))>=num-1;
where num is a table containing ints from 1 to 5000. One of the main downsides to this approach in MySQL that I have seen so far is that I can't have an empty attribute or node. If I do, the extracted node values will not line up with the extracted attribute values due to there being nothing to extract. In MSSQL, this wouldn't be a problem as the xml nodes act like a table structure but in MySQL I haven't found a decent way of doing this without string manipulation which I would rather not do if I can avoid it.
So, does anyone know of a better way for me to do this? If I absolutely have to, I will make a single procedure to take in 1 key and 1 amount at a time but I would like to avoid that if possible.
Tiada ulasan:
Catat Ulasan