What\'s the Difference Between INDEX and KEY in MySQL Table Creation?

Understanding the Equivalence of INDEX and KEY in MySQL

When creating tables in MySQL, you can encounter two terms for indexing data: INDEX and KEY. While both of these terms serve the same purpose, there is a subtle difference in their usage.

Definition of INDEX and KEY

• INDEX: A non-unique index used to speed up queries and improve data retrieval efficiency.
• KEY: A synonym for INDEX. It was introduced in MySQL for compatibility with other database systems.

Usage in Create Table Statements

In Create Table statements, you can use INDEX or KEY interchangeably. For example, the following two statements are equivalent:

CREATE TABLE tasks (
    task_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_id INT UNSIGNED NOT NULL DEFAULT 0,
    task VARCHAR(100) NOT NULL,
    PRIMARY KEY (task_id),
    INDEX parent (parent_id)
);

CREATE TABLE orders (
    order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    KEY order_date (order_date)
);

Unique vs. Non-Unique Indexes

While both INDEX and KEY can create non-unique indexes, it's important to note that PRIMARY KEY is unique by definition. This means that the value in a PRIMARY KEY column uniquely identifies each row in the table. INDEX and KEY, on the other hand, allow duplicate values.

Primary Key as KEY

In MySQL, you can define a PRIMARY KEY using the KEY keyword. However, it's recommended to use the PRIMARY KEY keyword explicitly to avoid confusion. For example:

CREATE TABLE customers (
    customer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (customer_id)
);

Recommendation

Although KEY and INDEX are synonymous in MySQL, it's generally recommended to use INDEX as it aligns with the ISO SQL standard and improves portability.

The above is the detailed content of What\'s the Difference Between INDEX and KEY in MySQL Table Creation?. For more information, please follow other related articles on the PHP Chinese website!