FIND_IN_SET() vs IN(): Understanding the Difference in MySQL
Question:
In MySQL, we have two tables: "orders" and "company." The "attachedCompanyIDs" column in "orders" stores a comma-separated list of IDs referencing rows in "company." When querying for company names associated with an order using FIND_IN_SET(), we get the expected results. However, using IN() instead returns only the first matching company. Why does this happen?
Answer:
The attachedCompanyIDs column is a scalar value of type VARCHAR. When using IN(), MySQL casts the string to an integer, truncating it at the first non-numeric character (i.e., a comma). This means that if attachedCompanyIDs contains multiple values, only the first one will be considered for the IN comparison.
解决方法:
Depending on your specific scenario, there are several workarounds for this issue:
Additional Considerations:
The above is the detailed content of MySQL FIND_IN_SET() vs IN(): Why Does IN() Only Return the First Match with Comma-Separated IDs?. For more information, please follow other related articles on the PHP Chinese website!