How to split a row based on a delimiter into multiple rows and retain the ID and ordering by using a single SQL statement for MySQL

Question

Scenario:

1. A MySQL table
2. A Delimiter (e.g. comma) is used to separate entities inside a string store in a column of the MySQL table
3. MySQL table contain 2 columns - ID and Countries_Involved

=======================================
| ID |   Countries_Involved           |
=======================================
| 1  | Japan, India, France           |
| 2  | Benin, Chad, Chile, Iraq, Iran |
=======================================

How do I use a single SQL statement (very good to have, if possible) to generate a table below:

====================================
| SN | ID | Country    |  Ordering |
====================================
| 1  | 1  | Japan      | 1         |
| 2  | 1  | India      | 2         |
| 3  | 1  | France     | 3         |
| 4  | 2  | Benin      | 1         |
| 5  | 2  | Chad       | 2         |
| 6  | 2  | Chile      | 3         |
| 7  | 2  | Iraq       | 4         |
| 8  | 2  | Iran       | 5         |
====================================

Answer 1

SQL is a real (but **DECLARATIVE**, not imperative) programming language! This has its pros and cons, see here.

But without the sarky comment from @RickJames :-), I was going to say the same thing. You'll always get problems like this when you don't properly normalise (you have what are called repeating groups - a breach of Codd's Rule no. 2 - scalar values).

As @RickJames says, you'll have to programme your way out of this one using an imperative language, but NOT SQL.