MySQL视图返回0行但原查询返回63行，AWS RDS迁移后异常排查

Hey there, I’ve run into this exact weirdness after migrating databases to RDS—let’s walk through the most likely culprits and fixes to get your view working again:

1. Check for Mismatched View Definitions #

First, make sure the view’s actual SQL matches the query you’re running manually. During migration, views can sometimes end up with hardcoded database names or table references that don’t align with your RDS setup.

• Run this to pull the view’s full definition:

  SHOW CREATE VIEW your_view_name;
  

• Compare the output line-by-line with the query that returns 63 rows. Look for differences like:
  • Qualified table names (e.g., old_local_db.table instead of rds_db.table)
  • Case sensitivity issues (RDS might enforce case-sensitive table names depending on the underlying OS/configuration)

2. Verify Permissions (Easy to Overlook) #

AWS RDS user permissions can differ drastically from your local setup. Even if you can run the raw query successfully, the view might be owned by a user with limited access, or your current user lacks permission to read the view’s underlying tables.

• Check your current user’s grants:

  SHOW GRANTS FOR current_user();
  

• Check grants specifically for the view:

  SHOW GRANTS ON your_view_name;
  

• If permissions are missing, grant the necessary access (replace placeholders as needed):

  GRANT SELECT ON your_rds_db.your_underlying_table TO 'your_rds_user'@'%';
  

3. Check Character Set/Collation Mismatches #

RDS defaults might use a different character set or collation than your local database, which can silently break string comparisons in the view. For example, a WHERE status = 'active' clause might fail if the column’s collation in RDS doesn’t match the literal’s.

• Check table and view collations:

  SELECT table_schema, table_name, table_collation 
  FROM information_schema.tables 
  WHERE table_name IN ('your_underlying_table', 'your_view_name');
  

• Check column-level collations for columns used in filters:

  SELECT column_name, collation_name 
  FROM information_schema.columns 
  WHERE table_name = 'your_underlying_table' 
    AND column_name IN ('column_used_in_where', 'other_filter_columns');
  

• If mismatches exist, alter the table/view to use a consistent collation (e.g., utf8mb4_unicode_ci):

  ALTER TABLE your_underlying_table MODIFY COLUMN your_column VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

4. Refresh View Metadata or Recreate the View #

Sometimes MySQL’s view metadata gets stale after migration, leading the optimizer to use an incorrect execution plan that returns no rows.

• First, try flushing the table cache:

  FLUSH TABLES your_view_name;
  

• If that doesn’t work, drop and recreate the view with your working query:

  DROP VIEW IF EXISTS your_view_name;
  CREATE VIEW your_view_name AS 
  -- Paste your working query here
  SELECT ... FROM ... WHERE ...;
  

5. Compare RDS vs. Local SQL Mode Settings #

AWS RDS often enables stricter sql_mode settings by default (like ONLY_FULL_GROUP_BY) that might not have been enabled locally. These settings can alter how your query (and thus the view) behaves, even without throwing errors.

• Check SQL mode on RDS:

  SELECT @@sql_mode;
  

• Compare it to your local MySQL instance’s sql_mode. If there are key differences, adjust the RDS parameter group to match your local setup (note: some parameters require a database restart to take effect).

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