ISO-3166 Country List for MySQL

Country tables are useful when you need a select box containing the countries in your application. It is a must have when you have multiple rows with country fields. ISO-3166 is the standard for 2-letter country codes so this post will show how to import ISO-3166 Country List for MySQL.

I showed every way possible to import the table. Hopefully everyone can benefit from this post.

phpMyAdmin screenshot of ISO-3166 Country List for MySQL

Method 1: Using mysql command line or phpMyAdmin

Download the SQL File or country.sql

MySQL Command Line

Replace <database name> with name of your database

mysql <database name> < country.sql


  1. Select the database you want to import the table to
  2. Click ‘Import’
  3. Select the file ( or country.sql)
  4. Click ‘Go’

Method 2: Using MySQL INFILE

If you want to have different table structure or different fields in your table, you can easily modify the below SQL snippets before importing the list.

Download the List

countrynames.txt (GitHub Gist by ihough)

Put the file somewhere reachable by the MySQL server

Create the Table

CREATE TABLE `country` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` char(2) CHARACTER SET latin1 NOT NULL COMMENT 'ISO 3166-1 alpha-2',
  `name` varchar(64) CHARACTER SET latin1 NOT NULL COMMENT 'ISO 3166-1 official English short name (Gazetteer order, w/o diacritics)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `name` (`name`)

Load the File into the Table

Replace /path/to/countrynames.txt with actual path of the file

LOAD DATA INFILE '/path/to/countrynames.txt' -- full path of the file
INTO TABLE country -- table name
IGNORE 22 LINES -- ignore 22 lines at the beginning of the file
     code, @ignore, @ignore, name,
  @ignore, @ignore, @ignore, @ignore,
  @ignore, @ignore, @ignore, @ignore,
  @ignore, @ignore, @ignore, @ignore,
  @ignore, @ignore, @ignore, @ignore,
  @ignore, @ignore, @ignore, @ignore,
  @ignore, @ignore, @ignore, @ignore
); -- taking 1st and 4th column ignoring the rest (26 columns)

Last words

Hope you were able to import ISO-3166 Country List for MySQL using this article. Let us know in the comments if you had any issues during this process.

See also

One thought on “ISO-3166 Country List for MySQL

  1. mysql_fetch_array($sth, MYSQL_ASSOC) – returns an associative array from the query whose statement handle is $sth. The elements will have keys which match the column names in the database, and if you use a foreach() on the array, you will find them ordered as you asked for them in the query.

Leave a Reply

Your email address will not be published. Required fields are marked *