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
MySQL Command Line
Replace <database name> with name of your database
mysql <database name> < country.sql
- Select the database you want to import the table to
- Click ‘Import’
- Select the file (country.sql.zip or country.sql)
- 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
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)
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.