如何使用SQL LIKE运算符匹配用户输入子串实现电影模糊查询

1. 核心问题：Model层SQL与参数修正 #

你原有SQL写法sql = "select * from movie where name like % + %s + %"存在两处错误：一是MySQL不支持+做字符串拼接，二是通配符%不能直接和参数占位符拼接。以下是两种符合要求的正确写法，均使用参数化查询避免SQL注入风险：

写法1：通配符绑定到参数上（推荐，兼容性更好） #

from model.DatabasePool import DatabasePool

class Movie:
    @classmethod
    def getRelevantMovies(cls,name):
        try:
            dbConn = DatabasePool.getConnection()
            cursor = dbConn.cursor(dictionary=True)
            # SQL只留占位符
            sql = "select * from movie where name like %s"
            # 把通配符拼到传入参数前后
            cursor.execute(sql, (f"%{name}%",))
            results = cursor.fetchall()
            return results
        finally:
            dbConn.close()

写法2：SQL层面用CONCAT拼接通配符 #

如果需要在SQL层面处理拼接，可使用MySQL原生的CONCAT函数：

# 不区分大小写匹配可改成 LOWER(name) like CONCAT('%', LOWER(%s), '%')
sql = "select * from movie where name like CONCAT('%', %s, '%')"
cursor.execute(sql, (name,))

2. 其他代码Bug同步修正 #

你现有代码还有两处语法/逻辑错误，修正后才能正常运行：

2.1 Database层语法错误 #

原有连接池初始化参数中host='localhost'后缺少逗号，会触发语法报错，修正后代码：

from mysql.connector import pooling

class DatabasePool:
    connection_pool = pooling.MySQLConnectionPool(
                               pool_name="ws_pool",
                               pool_size=5,
                               host='localhost',
                               database='assignment',
                               user='root',
                               password='abc123')

    @classmethod
    def getConnection(cls):
        dbConn = cls.connection_pool.get_connection()
        return dbConn

2.2 Controller层参数获取错误 #

Flask的GET请求查询参数不能直接作为路由函数参数接收，需要从request.args中提取，同时未匹配到结果返回404状态码更符合HTTP规范，修正后代码：

from flask import Flask,jsonify,request
from model.Movie import Movie

app = Flask(__name__)
@app.route('/search/movies', methods=['GET'])
def getMoviesBySubstring():
    try:
        name = request.args.get('name', '')
        movieList = Movie.getRelevantMovies(name)

        if len(movieList)>0:
            foundData = {"Movies":movieList}
            return jsonify(foundData),200
        else:
            return jsonify({}),404

    except Exception as err:
        print(err)
        return jsonify({}),500

if __name__=="__main__":
    app.run(debug=True)

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