SQLSyntaxErrorException: Unknown column 'ci.user_id' in 'where clause' - Troubleshooting Guide

Error querying database. Cause: java.sql.SQLSyntaxErrorException: Unknown column 'ci.user_id' in 'where clause'

The error may exist in file [C:\project\zhongxin\CiticMetal-Backend\customer\customer-biz\target\classes\mapper\CustomerInfoMapper.xml]

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: SELECT COUNT(*) FROM (SELECT ci.customer_id, ci.customer_name, ci.cbmm_cgs_name, ci.customer_type, ci.update_time FROM cm_customer_info ci WHERE ci.del_flag = '0' AND (ci.user_id = ? OR ci.user_id IS NULL) GROUP BY ci.customer_id) TOTAL

Cause: java.sql.SQLSyntaxErrorException: Unknown column 'ci.user_id' in 'where clause'; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'ci.user_id' in 'where clause'

前端报错，

======= Sql Logger ====================== select count(*) from ( select ci.customer_id, ci.customer_name, ci.cbmm_cgs_name, ci.customer_type, ci.update_time from cm_customer_info ci where ci.del_flag = '0' and (ci.user_id = 1646326606744612866 or ci.user_id is null) group by ci.customer_id ) TOTAL ======= Sql Execute Time: 0ms =======

idea控制台没有报错但是sql执行0ms

但是idea控制台的sql语句在数据库执行查询没有问题可以出数据，数据库有user_id这个列

Analysis and Troubleshooting

The error message 'Unknown column 'ci.user_id' in 'where clause'' indicates that the database cannot find a column named 'ci.user_id' within the 'where' clause of your SQL query. This discrepancy can arise from several common causes:

1. Missing Column: Double-check your database schema. Ensure the 'cm_customer_info' table actually contains a column named 'user_id'. If it's missing, you'll need to add it to the table.

2. Database Connection Issues: Verify that your database connection configuration is correct. Ensure the user account you're connecting with has sufficient permissions to access the 'cm_customer_info' table and its columns. Also, check for any typos or inconsistencies in the table and column names within your connection settings.

3. Mapping Discrepancies: If you are using an ORM (Object-Relational Mapper) or a database mapping framework, make sure the mapping between your Java code (or other programming language) and the database is accurate. Check the column mappings in your entity class, mapper XML file, or any other configuration related to your database interactions. The 'user_id' column might be named differently in your code than in the database.

Resolution Steps

To resolve this error, follow these steps:

1. Verify Database Schema: Use a database management tool (e.g., phpMyAdmin, SQL Server Management Studio) or command line queries to confirm the existence of the 'user_id' column in the 'cm_customer_info' table.

2. Review Database Connection: Check the connection settings in your application or framework. Make sure the username, password, database name, table name, and column names are spelled correctly and match your database environment.

3. Inspect Mapping: If you're using a database mapping framework, examine the mappings between your code and the database. Verify that the 'user_id' column is correctly mapped to its corresponding property in your entity class.

4. Regenerate Mappings: As a last resort, if the mappings seem correct, consider regenerating your database mapping files (e.g., entity classes, mapper XML files). This can help ensure that your code aligns with any changes in the database schema.

By following these troubleshooting steps, you should be able to pinpoint the root cause of the 'Unknown column 'ci.user_id' in 'where clause'' error and correct it. If you continue to face issues, provide more context about your development environment and framework to get tailored assistance.