But that on the good ground are they, which in an honest and good heart, having heard the word, keep it, and bring forth fruit with patience.
Luke 8:15
Create the initial table
If you have a corrupted database table or delete it by mistake and want to restore it from another correct table, you can use these methods.
Let’s say we have a flower shop and take orders over the phone. We assume that our database is called test.
We will first create an initial table with customers who call for flower orders. The table will contain several columns.
Identifier (the number of the record), date of the call, name of the customer, customer number, ordered product.
CREATE TABLE customer_calls_may (id int(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT); ALTER TABLE customer_calls_may ADD COLUMN call_date DATETIME NOT NULL; ALTER TABLE customer_calls_may ADD COLUMN customer_name varchar(200) NULL; ALTER TABLE customer_calls_may ADD COLUMN phone varchar(15) NULL; ALTER TABLE customer_calls_may ADD KEY (phone); ALTER TABLE customer_calls_may ADD COLUMN item varchar(200) NULL;
Now let us see the initial table structure.
DESC customer_calls_may; +----------------+---------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------------+------+-----+---------+----------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | call_date | datetime | NO | | NULL | | | customer_name | varchar(200) | YES | | NULL | | | phone | varchar(15) | YES | MUL | NULL | | | item | varchar(200) | YES | | NULL | | +----------------+---------------------------+------+-----+---------+----------------+
What is special about the structure of the table. The first row contains the index of the record. This index is the primary key for the table, and it will automatically increase by one for each subsequent record.
The second row contains an index of type datetime, which is a unique data type in MySQL.
The third row contains an index of type varchar. MySQL VARCHAR is a string whose length can vary up to 65535 bytes. MySQL stores a VARCHAR value as a 1-byte or 2-byte length prefix and the actual data. We use two hundred bytes for the name which is enough.
The fourth row contains an index of type varchar too but there is a one more property Key=MUL.
There are three possible values for the “Key” attribute:
- PRI => primary key.
- UNI => unique key.
- MUL => multiple key.
Whether the column is indexed:
If Key is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.
If Key is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.
If Key is UNI, the column is the first column of a UNIQUE index. (A UNIQUE index permits multiple NULL values, but you can tell whether the column permits NULL by checking the Null field.)
If Key is MUL, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.
Let’s enter data for several clients and see the table.
INSERT INTO customer_calls_may (call_date, customer_name, phone, item) VALUES ('2022-05-01 09:00:00', 'Fred Flintstone', '+1234567890', 'Rose');
INSERT INTO customer_calls_may (call_date, customer_name, phone, item) VALUES ('2022-05-01 10:00:00', 'Wilma Flintstone', '+0123456789', 'Tulip');
INSERT INTO customer_calls_may (call_date, customer_name, phone, item) VALUES ('2022-05-02 11:00:00', 'Barney Rubble', '+1122334455', 'Crocus');
INSERT INTO customer_calls_may (call_date, customer_name, phone, item) VALUES ('2022-05-03 13:23:20', 'Betty Rubble', '+6677889900', 'Cactus');
SELECT * FROM customer_calls_may;
+--------------------------+------------------+-------------+--------+
| id | call_date | customer_name | phone | item |
+--------------------------+------------------+-------------+--------+
| 1 | 2022-05-01 09:00:00 | Fred Flintstone | +1234567890 | Rose |
| 2 | 2022-05-01 10:00:00 | Wilma Flintstone | +0123456789 | Tulip |
| 3 | 2022-05-02 11:00:00 | Barney Rubble | +1122334455 | Crocus |
| 4 | 2022-05-03 13:23:20 | Betty Rubble | +6677889900 | Cactus |
+--------------------------+------------------+-------------+--------+
It can be seen that in the id column the values increase automatically.
Create an “exact” copy of an existing table
This is simple solution of creating a new table but more proper to say copy a table. The new table will have the same structure and data as the old one.
CREATE TABLE customer_calls_june AS SELECT * FROM customer_calls_may; SELECT * FROM customer_calls_june; +--------------------------+------------------+-------------+--------+ | id | call_date | customer_name | phone | item | +--------------------------+------------------+-------------+--------+ | 1 | 2022-05-01 09:00:00 | Fred Flintstone | +1234567890 | Rose | | 2 | 2022-05-01 10:00:00 | Wilma Flintstone | +0123456789 | Tulip | | 3 | 2022-05-02 11:00:00 | Barney Rubble | +1122334455 | Crocus | | 4 | 2022-05-03 13:23:20 | Betty Rubble | +6677889900 | Cactus | +--------------------------+------------------+-------------+--------+
All values look the same as the other table. But is everything okay in the structure?
DESC customer_calls_june;
DESC is an abbreviated version of the DESCRIBE command.
Another way to see the structure of a table is with the show command.
SHOW COLUMNS FROM customer_calls_june; +----------------+---------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------------+------+-----+---------+----------------+ | id | int unsigned | NO | | 0 | | | call_date | datetime | NO | | NULL | | | customer_name | varchar(200) | YES | | NULL | | | phone | varchar(15) | YES | | NULL | | | item | varchar(200) | YES | | NULL | | +----------------+---------------------------+------+-----+---------+----------------+
We lost all the keys in the structure. For this reason, the id column does not automatically increment in value. So this type of copying is not very accurate.
However, we can initialize the keys further. We use the following commands:
ALTER TABLE customer_calls_june ADD PRIMARY KEY (id); ALTER TABLE customer_calls_june MODIFY id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE customer_calls_june ADD KEY (phone); DESC customer_calls_june; +----------------+---------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------------+------+-----+---------+----------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | call_date | datetime | NO | | NULL | | | customer_name | varchar(200) | YES | | NULL | | | phone | varchar(15) | YES | MUL | NULL | | | item | varchar(200) | YES | | NULL | | +----------------+---------------------------+------+-----+---------+----------------+
In other cases you can add an auto incrementation to any column in a table, but before running the command MODIFY, ensure that column has a Primary index!
It is also possible to remove the keys. To remove the primary key we use the Drop command.
ALTER TABLE customer_calls_june DROP PRIMARY KEY;
However, using it in this direct way is likely to result in an error “Incorrect table definition; there can be only one auto column and it must be defined as a key”. We must first remove the auto increment property.
ALTER TABLE customer_calls_june MODIFY id INTEGER UNSIGNED; ALTER TABLE customer_calls_june DROP PRIMARY KEY; ALTER TABLE customer_calls_june DROP INDEX phone; DESC customer_calls_june; +----------------+---------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------------+------+-----+---------+----------------+ | id | int unsigned | NO | | NULL | | | call_date | datetime | NO | | NULL | | | customer_name | varchar(200) | YES | | NULL | | | phone | varchar(15) | YES | | NULL | | | item | varchar(200) | YES | | NULL | | +----------------+---------------------------+------+-----+---------+----------------+
Dump a database table into a definitions file via “mysqldump” command
Linux has a very handy tool for exporting a database table creation procedure into a file. In fact, this file contains a set of database commands for creating a table. We will create the file in the temporary folder. The tool we use is mysqldump:
mysqldump test customer_calls_may > /tmp/customer_calls_may.sql
Open the file with a text editor and find the row /*!40101 SET NAMES utf8mb4 */; and remove the string mb4. Save and exit.
Re-enter the MySQL shell, use the database test and delete the current table.
DROP TABLE customer_calls_may; SHOW TABLES; Empty set (0.000 sec)
Let’s recover the table from the file customer_calls_may.sql. Re-enter the Linux shell and write the commands:
mysql test < /tmp/customer_calls_may.sql mysql test
SHOW TABLES; +--------------------+ | Tables_in_test | +--------------------+ | customer_calls_may | +--------------------+ 1 row in set (0.000 sec) DESC customer_calls_may; +----------------+---------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------------+------+-----+---------+----------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | call_date | datetime | NO | | NULL | | | customer_name | varchar(200) | YES | | NULL | | | phone | varchar(15) | YES | MUL | NULL | | | item | varchar(200) | YES | | NULL | | +----------------+---------------------------+------+-----+---------+----------------+ 5 rows in set (0.001 sec)
A table exported and imported using this method retains its structure and data.
Comments