如何在MariaDB中根据数组值查询content表匹配行？

你可以使用MariaDB的IN操作符实现这个需求，它允许在WHERE子句中指定多个值进行匹配。

1. 直接编写SQL语句 #

假设content表中存储这些ID的字段名为id（如果实际字段名不同，请替换为真实名称），对应的SQL语句如下：

SELECT * FROM content
WHERE id IN (
    'tt11866324', 'tt1649418', 'tt12593682', 'tt4614584',
    'tt10954984', 'tt6718412', 'tt10648342', 'tt1655389',
    'tt10298810', 'tt14814040', 'tt1745960', 'tt8912936',
    'tt7144666', 'tt6710474', 'tt9411972', 'tt12262116',
    'tt3704428', 'tt7214954', 'tt8041270', 'tt16491324'
);

2. 通过编程语言动态生成SQL（以Python为例） #

如果idList在代码中维护，建议使用参数化查询避免SQL注入风险，示例代码如下：

import mysql.connector

idList = ["tt11866324","tt1649418","tt12593682","tt4614584","tt10954984","tt6718412","tt10648342","tt1655389","tt10298810","tt14814040","tt1745960","tt8912936","tt7144666","tt6710474","tt9411972","tt12262116","tt3704428","tt7214954","tt8041270","tt16491324"]

# 连接数据库
conn = mysql.connector.connect(
    host="你的数据库地址",
    user="用户名",
    password="密码",
    database="数据库名"
)
cursor = conn.cursor()

# 生成参数化占位符，MariaDB使用%s作为占位符
placeholders = ', '.join(['%s'] * len(idList))
sql = f"SELECT * FROM content WHERE id IN ({placeholders})"

# 执行查询
cursor.execute(sql, idList)
results = cursor.fetchall()

# 处理查询结果
for row in results:
    print(row)

# 关闭连接
cursor.close()
conn.close()

注意事项 #

• 确保content表中用于匹配的字段类型与ID类型一致（此处为字符串类型，SQL中需用单引号包裹）；
• 若ID数量极大（数千个以上），IN子句可能存在性能问题，可考虑将ID存入临时表，再通过JOIN完成查询。

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