jnbbender jnbbender - 3 months ago 11
MySQL Question

Modifying MySQL table using BASH array

I have a MySQL column which got incorrectly loaded with data. I created a script which reads the text file of 14 million primary keys I know got messed up. I have a basic bash

while
loop but instead of issuing a separate mysql command for every id, I'd like to do it for a set of id's (I do have 14 million after all).

currently:

while IFS='' read -r id || [[ -n "$id" ]]; do
mysql -e "UPDATE my_table SET direct = 1 WHERE id = $id" -u $USER -p$PASS db
done < "$1"


what I would like to do is read in a set of id's and say something like
WHERE id in ($#ids)
without having to perform a bunch of loops and modulos. Ideas?

Answer

If you have ids in an array, and they don't contain commas, then you can join the values by , and formulate an IN-query like this:

mysql -e "UPDATE my_table SET direct = 1 WHERE id IN ($(IFS=,; echo "${ids[*]}"))" -u $USER -p$PASS db

This solves one part of your problem.

Another part is to split the 14 million ideas to workable chunks. Something like this should work:

execute_update() {
    mysql -e "UPDATE my_table SET direct = 1 WHERE id IN $(IFS=,; echo "${ids[*]}")" -u $USER -p$PASS db
}

ids=()
while IFS='' read -r id; do
    ids+=($id)
    if [[ ${#ids[@]} = 200 ]]; then
        execute_update
        ids=()
    fi
done < "$1"

if [[ ${#ids[@]} < 200 ]]; then
    execute_update
    ids=()
fi
Comments