MySQL query splitting unexpectedly in Bash Script

Question

I am having issues and not understanding what the underlying cause is.

I have a table that has these three fields:

______________________________________________
|   cid    |    order_id    |    TxRefNum    |
----------------------------------------------

I am making a simple call in my bash script (there is literally no other code to start with)

#!/bin/bash

mysql --login-path=main-data -e "SELECT 
    cid,
    order_id,
    TxRefNum 
FROM database.orders_temp" |

while read this that other; do

    echo "$this  ||  $that  ||  $other"
done

I would expect to see the following:

__________________________________________________________
|    29    |    F0VIc - CHATEAU ROOFIN    |   5555555    |
----------------------------------------------------------

Instead my script is splitting the string $that into two different strings .. The echo is actually:

___________________________________________________
|    29    |    F0VIc    |    - CHATEAU ROOFIN    |
---------------------------------------------------

Do I have to set a delimiter when setting my variables in my while loop?? I am truly stumped!!

Your data might contain the delimiter. Quick way to test is by adding a replace in your query in which you just replace space with underscore. Also quick word of advice: Avoid using keywords/reserved words as parameters/variables: `this` is not the handiest name for the parameter. — Norbert van Nobelen, Jul 07 '17 at 19:24
LOL I realize `this` (no pun intended) -- Those obviously aren't my "real" variable names ;-) — Zak, Jul 07 '17 at 21:17

cwallenpoole · Accepted Answer · 2017-07-07T19:45:00.823

Getting output from the mysql command formatted in an intelligent way is problematic. In your case bash is interpreting the as a delimiter. You need to split a different way. I was able to get this working. You'll note the | in the query as well at the IFS line at the tope

#!/bin/bash

IFS='|' # set the delimiter
mysql --login-path=main-data -e "SELECT
    29 as cid, '|',
    'F0VIc - CHATEAU ROOFIN' as order_id,
    '|',
    5555555 as TxRefNum
FROM dual" |
while read this that other; do
    echo "$this  ||  $that  ||  $other"
done

MySQL query splitting unexpectedly in Bash Script

1 Answers1