MySQL查询无匹配结果时返回NULL而非空值的实现方法

Got it, let's sort this out for you. You're querying the mol_source table to get an id matching a specific src_compound_id, and you want the get_id bash variable to be set to NULL instead of being empty when there's no matching record. Here are two reliable ways to handle this:

Option 1: Handle it directly in the MySQL query #

You can use MySQL's COALESCE function with an aggregate to guarantee a result row even when there's no match:

get_id=$(mysql --login-path=local -N -D uni -e "SELECT COALESCE(MAX(id), 'NULL') FROM mol_source WHERE src_compound_id='$src_compound_id_cutted'")

How this works: #

• MAX(id) returns NULL when there are no matching rows
• COALESCE replaces that NULL with the string 'NULL' so your bash variable never ends up empty
• Aggregate functions like MAX ensure the query always returns exactly one row, avoiding empty output

Option 2: Post-process in bash #

If you prefer to keep your original SQL query intact, you can check the variable after running the query and set it to NULL if it's empty:

# Run your original query
get_id=$(mysql --login-path=local -N -D uni -e "select id from mol_source where src_compound_id='$src_compound_id_cutted'")

# Replace empty value with NULL
if [[ -z "$get_id" ]]; then
    get_id="NULL"
fi

This approach is straightforward and keeps your SQL logic simple, handling the null substitution entirely in bash.

Quick note on security #

If $src_compound_id_cutted comes from untrusted input, directly inserting it into your SQL query risks SQL injection. For better security, use parameterized queries instead:

get_id=$(mysql --login-path=local -N -D uni -e "select id from mol_source where src_compound_id=?" --execute="$src_compound_id_cutted")

内容的提问来源于stack exchange，提问作者antoine Kourmanalieva