@Yanping Sun I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .
Issue:
- how to clear the query cache for a MySQL stored procedure and whether the execution plan cache gets cleared every time the procedure is updated.
Solution:
- The user was using v8.0 Later and found out there was a bug in their procedure, so it might not have been related to cache (I was guessing the unchanged result was related to cache). However, Mysql workbench didn't give any error then it suddenly did.
In case if you need additional information
The execution plan should be flushed if the stored procedure or the underlying table or view is altered using DDL by design. If you don’t see this, we would recommend to leverage FLUSH TABLES, to explicitly clear the cache. For more details on caching behavior of MySQL, please read the documentation below
MySQL :: MySQL 5.7 Reference Manual :: 8.10.4 Caching of Prepared Statements and Stored Programs
Regards
Geetha