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.

Method 1: Using mysql command line or phpMyAdmin

Download the SQL File

country.sql.zip or country.sql

MySQL Command Line

Replace <database name> with name of your database

mysql <database name> < country.sql

phpMyAdmin

1. Select the database you want to import the table to
2. Click ‘Import’
3. Select the file (country.sql.zip 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
FIELDS TERMINATED BY '; '
LINES TERMINATED BY '\n'
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

• WordPress Backup Script