Setting Up the world Database

Table of Contents
1 Preface and Legal Notices ......................................................................................................... 1
2 Installation ................................................................................................................................. 2
3 History ...................................................................................................................................... 3

This document describes world sample database installation, structure, usage, and history.

The sample data used in the world database is Copyright Statistics Finland, http://www.stat.fi/
worldinfigures.

For legal information, see the Legal Notices.

For help with using MySQL, please visit the MySQL Forums, where you can discuss your issues with
other MySQL users.

Document generated on: 2022-12-22 (revision: 74777)

1 Preface and Legal Notices

This document describes world sample database installation, structure, usage, and history.

Legal Notices
Copyright © 2005, 2023, Oracle and/or its affiliates.

This software and related documentation are provided under a license agreement containing
restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly
permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate,
broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any
form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless
required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-
free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone
licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated
software, any programs embedded, installed or activated on delivered hardware, and modifications of
such programs) and Oracle computer documentation or other Oracle data delivered to or accessed by
U.S. Government end users are "commercial computer software" or "commercial computer software
documentation" pursuant to the applicable Federal Acquisition Regulation and agency-specific
supplemental regulations. As such, the use, reproduction, duplication, release, display, disclosure,
modification, preparation of derivative works, and/or adaptation of i) Oracle programs (including any
operating system, integrated software, any programs embedded, installed or activated on delivered
hardware, and modifications of such programs), ii) Oracle computer documentation and/or iii) other
Oracle data, is subject to the rights and limitations specified in the license contained in the applicable
contract. The terms governing the U.S. Government's use of Oracle cloud services are defined by the
applicable contract for such services. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management
applications. It is not developed or intended for use in any inherently dangerous applications, including
applications that may create a risk of personal injury. If you use this software or hardware in dangerous

1
 Setting Up the world Database

applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and
other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any
damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be
trademarks of their respective owners.

Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC
trademarks are used under license and are trademarks or registered trademarks of SPARC
International, Inc. AMD, Epyc, and the AMD logo are trademarks or registered trademarks of Advanced
Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information about content,
products, and services from third parties. Oracle Corporation and its affiliates are not responsible
for and expressly disclaim all warranties of any kind with respect to third-party content, products,
and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle
Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to
your access to or use of third-party content, products, or services, except as set forth in an applicable
agreement between you and Oracle.

This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the
following terms:

You may create a printed copy of this documentation solely for your own personal use. Conversion
to other formats is allowed as long as the actual content is not altered or edited in any way. You shall
not publish or distribute this documentation in any form or on any media, except if you distribute the
documentation in a manner similar to how Oracle disseminates it (that is, electronically for download
on a Web site with the software) or on a CD-ROM or similar medium, provided however that the
documentation is disseminated together with the software on the same medium. Any other use, such
as any dissemination of printed copies or use of this documentation, in whole or in part, in another
publication, requires the prior written consent from an authorized representative of Oracle. Oracle and/
or its affiliates reserve any and all rights to this documentation not expressly granted above.

Access to Oracle Support for Accessibility

Oracle customers that have purchased support have access to electronic support through My Oracle
Support. For information, visit
https://www.oracle.com/corporate/accessibility/learning-support.html#support-tab.

2 Installation
To enable MySQL users to perform manipulation of the world sample database using MySQL, the
data set is available as a set of three tables:

• country: Information about countries of the world.

• city: Information about some of the cities in those countries.

• countrylanguage: Languages spoken in each country.

The world.sql file containing the world database is available for download at https://dev.mysql.com/
doc/index-other.html. The file is provided as a compressed tar file or Zip archive.

Note

For users of MySQL 5.7 and higher, a world_x variant also exists that
implements features related to X DevAPI. See Setting Up the world_x
Database.

To install the world sample database, follow these steps:

2
 Setting Up the world Database

1. Download the installation archive to a temporary location such as C:\temp\ or /tmp/ and unpack
it. Unpacking the archive results in a directory named world-db containing a single file named
world.sql.

2. Connect to the MySQL server using the mysql command-line client with the following command:
$> mysql -u root -p

Enter your password when prompted. A non-root account can be used, provided that the account
has privileges to create new databases.

3. Execute the world.sql script to create the database structure and insert the data using the
following command:
mysql> SOURCE C:/temp/world.sql;

Replace the path to the world.sql file with the actual path on your system.

Note

On Windows, use slashes rather than backslashes when executing the

SOURCE command.

4. To confirm that the sample world database is installed correctly, execute the following statements.
You should see output similar to that shown here.
mysql> USE world;
Database changed

mysql> SHOW TABLES;

+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM city;

+----------+
| COUNT(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.02 sec)

mysql> SELECT COUNT(*) FROM country;

+----------+
| COUNT(*) |
+----------+
| 239 |
+----------+
1 row in set (0.00 sec)

You now have the world sample database installed.

Another popular sample database is the Sakila database. For additional details, see http://
dev.mysql.com/doc/sakila/en/.

3 History
September 2016

Prior releases used mixed-case table names. Because MySQL Shell is case-sensitive, table names are
changed to lowercase.

3
 Setting Up the world Database

December 2019

The world.sql file was changed to address several issues:

• The world database used latin1, but MySQL as of 8.0 uses a default character set of utf8mb4.
Converting the database to use utf8mb4 brings it up to date with MySQL 8.0, while retaining
compatibility with older series.

Conversion of the database to utf8mb4 was done as follows:

USE world;

-- turn off foreign key checking; otherwise, the ALTER TABLE

-- statements fail with incompatible foreign key errors.
SET SESSION foreign_key_checks=0;

-- convert database and tables to utf8mb4

ALTER DATABASE world CHARACTER SET utf8mb4;
ALTER TABLE city CONVERT TO CHARACTER SET utf8mb4;
ALTER TABLE country CONVERT TO CHARACTER SET utf8mb4;
ALTER TABLE countrylanguage CONVERT TO CHARACTER SET utf8mb4;

-- re-enable foreign key checking

SET SESSION foreign_key_checks=0;

• MySQL Shell requires a character set of utf8mb4 for X Protocol connections. Using such a
connection to load a latin1-encoded world.sql file produces errors.

Using mysqldump with options of --default-character-set=utf8mb4 to set the character set

and --set-charset so the dump includes SET NAMES for that character set writes a utf8mb4-
encoded dump file. Changing the encoding of world.sql to utf8mb4 permits it to be loaded
without errors in MySQL Shell using X Protocol connections.

• MySQL 8.0.17 deprecates these features:

• Number of digits in floating-point column definitions; for example, FLOAT(10,2).

• Display width in integer column definitions; for example, INT(10).

The world.sql file used both features, causing load warnings in MySQL 8.0.17 and higher.

To avoid number of digits in FLOAT columns, it is necessary to manually alter the relevant columns.
That was done as follows, by altering FLOAT(M,D) to DECIMAL(M,D):
USE world;

ALTER TABLE country

MODIFY SurfaceArea DECIMAL(10,2) NOT NULL DEFAULT '0.00',
MODIFY LifeExpectancy DECIMAL(3,1) DEFAULT NULL,
MODIFY GNP DECIMAL(10,2) DEFAULT NULL,
MODIFY GNPOld DECIMAL(10,2) DEFAULT NULL;
ALTER TABLE countrylanguage
MODIFY Percentage DECIMAL(4,1) NOT NULL DEFAULT '0.0';

To produce integer column definitions without display widths in the dump file, it suffices to use
mysqldump from MySQL 8.0.19 or higher.