时间戳

首页 > 解决方案 > Bash script for reads from csv and use it in mysql query

mysql - Bash script for reads from csv and use it in mysql query

问题描述

I need a bash script which reads from csv 2 values per line and use it in mysql query.

The .csv contains about +3k lines like this:

FA63MZ6,200000000163,2020-06-27,CF,Name Surname1 Surname1
FA63MZ6,200000000163,2020-06-27,CF,Name2 Surname2 Surname2

I would like to extract "date" and "Name Surname1 Surname1" as variables to use it in a mysql query.

I am triying with :

echo "select status from invoices where date_created= \"${date01}\" and customer_id in (select id from customers where name = \"${name01}\" );" | mysql -u user -pPassword -D dbname | grep -v status

Ideally this script could be create a .csv with the result of query:

FA63MZ6,200000000163,2020-06-27,CF,Name Surname1 Surname1,paid
FA63MZ6,200000000164,2020-06-27,CF,Name2 Surname2 Surname2,not_paid

Thanks in advance,

标签： mysqlbashcsv

解决方案

像这样：

{
    while IFS=, read -r f1 f2 f3 f4 f5; do
        printf "SELECT status FROM invoice WHERE date_created=\047$f2\047 AND
        customer_id IN (
        SELECT id FROM customers WHERE name = \047$f5\047);"
        echo
    done < Input_File.csv
} | mysql --skip-column-names -u user -pPassword -D dbname

\047ascii 序列是单引号字符。

不使用 MySQL 进行测试

while IFS=, read -r f1 f2 f3 f4 f5; do
    printf "SELECT status FROM invoice WHERE date_created=\047$f2\047 AND
    customer_id IN (
    SELECT id FROM customers WHERE name = \047$f5\047);"
    echo
done < Input_File.csv

输出

SELECT status FROM invoice WHERE date_created='200000000163' AND
    customer_id IN (
    SELECT id FROM customers WHERE name = 'Name Surname1 Surname1');
SELECT status FROM invoice WHERE date_created='200000000163' AND
    customer_id IN (
    SELECT id FROM customers WHERE name = 'Name2 Surname2 Surname2');

推荐阅读