MySQL创建视图时出现Error 1064错误，关联SQL查询求助

Let's walk through the issues in your code and get that view working properly. Error 1064 almost always points to a syntax or usage violation specific to MySQL's rules—here's what's going wrong and how to fix it:

Key Issues Triggering Error 1064 #

• Session Variables Aren't Allowed in Views: Your code uses @regex_bl and @regex_sl (user-defined session variables), but MySQL doesn't permit referencing these variables in a view's definition. The view parser sees this as invalid syntax, hence the 1064 error.
• Truncated Query: Your provided code cuts off at AS SUSP...—make sure your final column definition is complete (I'll assume it's AS SUSPICIOUS_LIST for the fix below).

Corrected View Creation Code #

Instead of pre-defining variables, embed the GROUP_CONCAT logic directly into your IF conditions. This keeps the view definition self-contained and compliant with MySQL's rules:

-- Optional: Increase group_concat_max_len if your blacklist/suspect lists have many words
SET SESSION group_concat_max_len = 1000000;

CREATE VIEW your_view_name AS
SELECT 
    p_f_A.ID,
    p_f_A.id_st,
    p_f_A.source_query,
    p_f_A.st_text,
    IF(p_f_A.st_image REGEXP 'http', p_f_A.st_image, 'NO IMAGE') AS st_IMAGE_URL,
    IF(p_f_A.st_text REGEXP (SELECT GROUP_CONCAT(DISTINCT word ORDER BY word DESC SEPARATOR '|') FROM t_black_list), 'YES', 'NO') AS BLACK_LIST,
    IF(p_f_A.st_text REGEXP (SELECT GROUP_CONCAT(DISTINCT word ORDER BY word DESC SEPARATOR '|') FROM t_suspect_list), 'YES', 'NO') AS SUSPICIOUS_LIST
-- Don't forget to add your FROM clause here! You referenced p_f_A but didn't include the table/join
FROM your_main_table AS p_f_A;

Important Notes #

1. Replace Placeholders: Swap your_view_name and your_main_table with your actual view and table names.
2. GROUP_CONCAT Length: If your t_black_list or t_suspect_list has a lot of entries, the default group_concat_max_len (1024 characters) might truncate your regex pattern. The SET SESSION command above temporarily increases this limit for your current session.
3. Simplify REGEXP Checks: You don't need = 1 after REGEXP—MySQL treats the match result as a boolean directly in the IF condition, which makes the code cleaner.

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