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.

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

phpMyAdmin screenshot of country table

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 (via opengeocode.org)

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)

 

Leave a Reply

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