How to load ISO 639-3 tables provided by SIL into MYSQL

ISO 639-3 language codes are provided for download by SIL under http://www.sil.org/iso639-3/download.asp. As the codes are stored as a tab separated list and a SQL table create command is given, it is pretty easy to upload the data into MYSQL.

All what is written beneath applies to MySQL 5. For older versions Unicode can't be used, which shouldn't be necessary for this tables.

Here is how:

If you didn't install MYSQL yet, refer to your distribution how to do so.
Afterwards login as admin

mysql -u admin -p

and paste the following code to create a new user that has all rights on databases of the scheme 'username_*' where username is your username. Replace "username" with your username and "password" with a passwort of your choice.

CREATE USER 'username'@ '%' IDENTIFIED BY 'password';

GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY 'password' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNE
CTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

GRANT ALL PRIVILEGES ON `username_%`.* TO 'username'@'%';

Login as user and create the database with the following code. Again substitute "username" with your username.

CREATE DATABASE `username_ISO639-3` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

Now copy the create table code from the download site of SIL and paste it to MYSQL, you might have to remove the '-' in the TABLE NAME: 'CREATE TABLE ISO_639_3' instead 'CREATE TABLE ISO_639-3'.
After you created the table you can import the tab separated list:

LOAD DATA LOCAL INFILE 'iso-639-3_20070814.tab' INTO TABLE ISO_639_3 CHARACTER SET utf8 FIELDS TERMINATED BY '\t' IGNORE 1 LINES;

You maybe have to add a line break at the end of the last line to omit a warning.

If the import was successful you can start to ask for stuff like:
Give me all macrolanguages that don't have a ISO 639-2 equivalent code.

select * from ISO_6393 WHERE Scope='M' and Part2T = '';

loc.gov ISO 639-2 table

The Library of Congress has a file for download with the codes for ISO 639-2:
http://www.loc.gov/standards/iso639-2/ascii_8bits.html

MySQL create table and import command:

CREATE TABLE `ISO_639_2` (
`Part2B` char(3) collate utf8_bin default NULL,
`Part2T` char(3) collate utf8_bin default NULL,
`Part1` char(2) collate utf8_bin default NULL,
`Ref_Name_EN` varchar(150) collate utf8_bin NOT NULL,
`Ref_Name_FR` varchar(150) collate utf8_bin NOT NULL
)

LOAD DATA LOCAL INFILE 'ISO-639-2_utf-8.txt' INTO TABLE ISO_639_2 CHARACTER SET utf8 FIELDS TERMINATED BY '|';

Select all entries with different T/B codes:

select * from ISO_639_2 where Part2T != '';

Modify table before import

You have to cut off the Byte Order Mark as MySQL will treat it as a normal character, an the code aar for Afar will be truncated.

Further more you maybe won't need the entry
qaa-qtz|||Reserved for local use|réservée à l'usage local
As this code wouldn't fit into the above schema (you will receive a warning).