Database:MariaDB Enum

From Juneday education
Jump to: navigation, search

Short introduction with example

This is just a short introduction with code examples for how to use enum types in MariaDB/MySQL.

Creating the table

We'll dig right into it! To create a table with a column which has an enum type, use the following syntax:

CREATE TABLE `fruits` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `fruit` enum('Apple','Orange','Banana') NOT NULL,
  UNIQUE KEY `id` (`id`)
);

Verifying if it works

Let's first insert one of the permitted fruits, an Apple:

MariaDB [enum_example]> INSERT INTO fruits (fruit) VALUES ('Apple');
MariaDB [enum_example]> SELECT * FROM fruits;
+----+-------+
| id | fruit |
+----+-------+
|  1 | Apple |
+----+-------+
1 row in set (0.00 sec)

In order to verify that our enum works, let's first try to insert an illegal fruit value with the default settings:

MariaDB [enum_example]> insert into fruits(fruit) values('Pineapple');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [enum_example]> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'fruit' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

MariaDB [enum_example]> select * from fruits;
+----+-------+
| id | fruit |
+----+-------+
|  1 | Apple |
|  2 |       |
+----+-------+
2 rows in set (0.00 sec)
MariaDB [enum_example]>

Hmm, the row was inserted but with a blank value for the pineapple fruit.

Delete the row with id 2!

MariaDB [enum_example]> delete from fruits where id = 2;
Query OK, 1 rows affected (0.01 sec)

MariaDB [enum_example]> select * from fruits;
+----+-------+
| id | fruit |
+----+-------+
|  1 | Apple |
+----+-------+
1 row in set (0.00 sec)

Let's instead try to set the sql_mode variable to STRICT_ALL_TABLES and try again with the bad fruit:

MariaDB [enum_example]> SET sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

MariaDB [enum_example]> insert into fruits(fruit) values('Pineapple');
ERROR 1265 (01000): Data truncated for column 'fruit' at row 1
MariaDB [enum_example]> select * from fruits;
+----+-------+
| id | fruit |
+----+-------+
|  1 | Apple |
+----+-------+
1 row in set (0.00 sec)

MariaDB [enum_example]>

That's more like it. Read more about ENUM and sql_mode.

We hope you had use for this short introduction to the enum type!

Here is the table definition:

MariaDB [enum_example]> DESC fruits;
+-------+---------------------------------+------+-----+---------+----------------+
| Field | Type                            | Null | Key | Default | Extra          |
+-------+---------------------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned             | NO   | PRI | NULL    | auto_increment |
| fruit | enum('Apple','Orange','Banana') | NO   |     | NULL    |                |
+-------+---------------------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

Here's how we created the database:

$ mysql -u root -p mysql
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.0.36-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [mysql]> create database enum_example;
MariaDB [mysql]> GRANT ALL PRIVILEGES ON enum_example.* TO 'dbuser'@'localhost';