For another database in similar situtation even above solution didnt work. Although a very similar approach worked. I ran this and it worked.
UPDATE wp_posts
SET post_title = CONVERT(CAST(CONVERT(post_title USING latin1) AS BINARY) USING utf8mb4)
WHERE post_title IS NOT NULL;
Thanks very much @bcworkz A lot of chatgpt and using your hints, it worked out
What I did is this
Step 1: Strip charset info (BLOB = raw bytes).
Step 2: Redefine as UTF-8 (utf8mb4) without changing the bytes.
— Example for wp_posts.post_title
ALTER TABLE wp_posts CHANGE post_title post_title BLOB;
ALTER TABLE wp_posts CHANGE post_title post_title TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
