>>
Copying tables with and without data
Let's start
Connect to Manticore using the mysql client:
mysql -h0 -P9306
Create a test table and populate it with some data:
create table demo_table (info text, value integer) morphology = 'stem_en' min_infix_len = '3';
show table demo_table settings;
insert into demo_table (info, value) values ('test 1 [DEM]', 1), ('test 2 [TES]', 2);
select * from demo_table;
Copying schema from another table
To create a table using the standard create table
command, we will replace the table schema description with the like command and specify the name of the table from which we want to adopt the data schema:
create table demo_like like demo_table;
Check the schema of both tables:
desc demo_table;
desc demo_like;
Also, check if the settings were transferred to the new table:
show table demo_like settings;
And the presence of data in these tables:
select * from demo_table;
select * from demo_like;
As we can see, there is no data in the newly created table.
Creating a table while preserving the schema and data
Now, let's create another table, but this time we will transfer the data from the original table as well. This process is as simple as copying, but we also need to specify the with data parameter:
create table demo_withdata like demo_table with data;
Check the result:
desc demo_withdata;
show table demo_withdata settings;
select * from demo_withdata;
Both the schema and data are saved. In situations where we no longer need to retain the old table or simply need to rename it, we can utilize the ALTER TABLE <table name> RENAME <new table name> command. Let's review the current tables available:
show tables;
Rename the original table demo_table to demo:
alter table demo_table rename demo;
Check the list of tables now:
show tables;
And check the presence of data, schema, and settings of the table:
select * from demo;
desc demo;
show table demo settings;