Loading ISO Country Codes and Emoji Flags into MySQL

Loading ISO Country Codes and Emoji Flags into MySQL

~ 3 min read

country codes country emojis iso 3166-1

If you’re building a global application or a multilingual platform, having access to ISO 3166-1 alpha-2 country codes can be essential. These two-letter codes (like GB for the United Kingdom of Great Britain and Northern Ireland or FR for France) are widely used in databases and APIs. In this article, I’ll walk through how to:

  • Fetch ISO country codes and names
  • Load them into a MySQL table
  • Generate the corresponding country flag emojis using a SQL expression

Step 1: Fetch ISO Country Codes and Names

You can get a reliable dataset from the official ISO site or use public datasets. For simplicity, we’ll use a JSON source via Node.js (or use it directly from your language of choice).

Example JSON (shortened for illustration):

[
  {
    "code": "US",
    "name": "United States"
  },
  {
    "code": "FR",
    "name": "France"
  },
  {
    "code": "DE",
    "name": "Germany"
  }
]

Step 2: Create a MySQL Table for Countries

Use the following SQL to create the table:

CREATE TABLE countries
(
    id   INT AUTO_INCREMENT PRIMARY KEY,
    code CHAR(2)      NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL
);

Step 3: Insert Country Codes and Names

You can load the data manually or via a script. Here’s an example using INSERT for three countries:

INSERT INTO countries (code, name)
VALUES ('US', 'United States'),
       ('FR', 'France'),
       ('DE', 'Germany');

Or use a script (e.g. in Node.js, PHP, or Python) to automate this from a JSON source. Many database clients also allow import from a CSV file, which may prove the easiest import method.

Step 4: Generate Emoji Flags in SQL

Emoji flags are generated using a clever trick with regional indicator symbols. Each letter is mapped to a Unicode character starting from 🇦 U+1F1E6. Here’s the logic:

  • 4,036,986,725 is the Unicode code point right before Regional Indicator Symbol Letter A ‘🇦’
  • Add 4036986725 to the ASCII value of each uppercase letter to get the flag component

MySQL Expression to Convert Country Code to Emoji

SELECT code,
       name,
       CONCAT(
               CHAR(ASCII(SUBSTRING(code, 1, 1)) + 4036986725),
               CHAR(ASCII(SUBSTRING(code, 2, 1)) + 4036986725)
       ) AS flag_emoji
FROM countries;

This will output:

codenameflag_emoji
USUnited States🇺🇸
FRFrance🇫🇷
DEGermany🇩🇪

Final Tips

  • Ensure your MySQL table is using utf8mb4 to support full Unicode (including emoji).
  • If using Laravel, you can add a virtual accessor or even store the emoji in a generated column.
  • This setup is great for drop-down lists, form selectors, or flag-based visualisations.

Summary

With just a simple dataset and a bit of Unicode math, you can enrich your applications with ISO-standardised country info and matching flag emojis. This is a lightweight, efficient addition that can really boost your UI/UX when dealing with international audiences.

all posts →