Looping over mysql query results in bash
 

I am querying a single string column in a table. The string values have spaces in them. I want to loop over each value in bash. I set IFS to split lists on newlines instead of spaces. When I try this, it is splitting the list of results on the actual character 'n', not the newline '\n'.

DATA=`mysql -u root -ppassword --silent 'SELECT name FROM table_a;'`

IFS=$'\n'
for i in $DATA; do
echo "item = $i"
done

unset IFS

These string values in the column:

spam alot
eggs
mandate today
foo

would produce this output from the script:

item = spam alot
eggs
ma
item = date today
foo


Why is IFS=$'\n' splitting on 'n' and not '\n'?

Q: have you tried (double-quotes expand metacharacters; single quotes don't)

@14moose: No actually he has the syntax correct. the $'string' structure is designed for expanding backslashed escapes into their ascii equivalents. Just quoting them doesn't do that. The bash man page section on QUOTING details it.

I don't see anything wrong with the script as written, and I don't get the same problem in testing. Are you perhaps using #!/bin/sh instead of #!/bin/bash?

(Edit: I'll bet that's it. Using #!/bin/sh reproduces the behavior. $'string' is a bash-specific function that's not available in posix-compliance mode.)

You can always use an actual newline instead of the string substitution, in any case.
BTW, please use [code][/code] tags around your code, to improve readability and to preserve formatting.

The OP's code worked for me as written. Could he have set his escape character to something else?

Setting the scripts first line to /bin/bash instead of /bin/sh worked. I guess Ubuntu defaults /bin/sh to use Dash, and I was reading the docs for Bash. Thanks for the help.

use a while read loop instead
No need to mess with IFS. Prevents from forgetting to reset IFS back