Despite a MariaDB developer's best planning, occasionally one needs to change the structure or other aspects of tables. This is not very difficult, but some developers are unfamiliar with the syntax for the functions used in MariaDB to accomplish this. And some changes can be very frustrating. In this article we'll explore the ways to alter tables in MariaDB and we'll give some precautions about related potential data problems.

Before Beginning

For the examples in this article, we will refer to a database called db1 containing a table called clients. The clients table is for keeping track of client names and addresses. To start off, we'll enter a statement to see what the table looks like:

This is a very simple table that will hold very little information. However, it's sufficient for the examples here in which we will change several of its columns. Before doing any structural changes to a table in MariaDB, especially if it contains data, one should make a backup of the table to be changed. There are a few ways to do this, but some choices may not be permitted by your web hosting company. Even if your database is on your own server, though, the utility is typically the best tool for making and restoring backups in MariaDB, and it's generally permitted by web hosting companies. To backup the clients table with , we will enter the following from the command-line:

As you can see, the username and password are given on the first line. On the next line, the --add-locks option is used to lock the table before backing up and to unlock automatically it when the backup is finished. There are many other options in that could be used, but for our purposes this one is all that's necessary. Incidentally, this statement can be entered in one line from the shell (i.e., not from the mariadb client), or it can be entered on multiple lines as shown here by using the back-slash (i.e., /) to let the shell know that more is to follow. On the third line above, the database name is given, followed by the table name. The redirect (i.e., >) tells the shell to send the results of the dump to a text file called clients.sql in the current directory. A directory path could be put in front of the file name to create the file elsewhere. If the table should need to be restored, the following can be run from the shell:

Notice that this line does not use the mariadb-dump utility. It uses the mariadb client from the outside, so to speak. When the dump file (clients.sql) is read into the database, it will delete the clients table and it's data in MariaDB before restoring the backup copy with its data. So be sure that users haven't added data in the interim. In the examples in this article, we are assuming that there isn't any data in the tables yet.

Basic Addition and More

In order to add a column to an existing MariaDB table, one would use the statement. To demonstrate, suppose that it has been decided that there should be a column for the client's account status (i.e., active or inactive). To make this change, the following is entered:

This will add the column status to the end with a fixed width of two characters (i.e., AC for active and IA for inactive). In looking over the table again, it's decided that another field for client apartment numbers or the like needs to be added. That data could be stored in the address column, but it would better for it to be in a separate column. An statement could be entered like above, but it will look tidier if the new column is located right after the address column. To do this, we'll use the AFTER option:

By the way, to add a column to the first position, you would replace the last line of the SQL statement above to read like this:

Before moving on, let's take a look at the table's structure so far:

Changing One's Mind

After looking over the above table display, it's decided that it might be better if the status column has the choices of 'AC' and 'IA' enumerated. To make this change, we'll enter the following SQL statement:

Notice that the column name status is specified twice. Although the column name isn't being changed, it still must be respecified. To change the column name (from status to active), while leaving the enumerated list the same, we specify the new column name in the second position:

Here we have the current column name and then the new column name, along with the data type specifications (i.e., ENUM), even though the result is only a name change. With the CHANGE clause everything must be stated, even items that are not to be changed.

In checking the table structure again, more changes are decided on: The column address is to be renamed to address1 and changed to forty characters wide. Also, the enumeration of active is to have 'yes' and 'no' choices. The problem with changing enumerations is that data can be clobbered in the change if one isn't careful. We've glossed over this possibility before because we are assuming that clients is empty. Let's take a look at how the modifications suggested could be made with the table containing data:

The first SQL statement above changes address and modifies active in preparation for the transition. Notice the use of a MODIFY clause. It works the same as CHANGE, but it is only used for changing data types and not column names. Therefore, the column name isn't respecified. Notice also that there is a comma after the CHANGE clause. You can string several CHANGE and MODIFY clauses together with comma separators. We've enumerated both the new choices and the old ones to be able to migrate the data. The two statements are designed to adjust the data accordingly and the last statement is to remove the old enumerated choices for the status column.

In talking to the boss, we find out that the client_type column isn't going to be used. So we enter the following in MariaDB:

This deletes client_type and its data, but not the whole table, obviously. Nevertheless, it is a permanent and non-reversible action; there won't be a confirmation request when using the mariadb client. This is how it is with all MariaDB DROP statements and clauses. So be sure that you want to delete an element and its data before using a DROP. As mentioned earlier, be sure that you have a backup of your tables before doing any structured changes.

The Default

You may have noticed that the results of the statements shown before have a heading called 'Default' and just about all of the fields have a default value of NULL. This means that there are no default values and a null value is allowed and are used if a value isn't specified when a row is created. To be able to specify a default value other than NULL, an statement can be entered with a SET clause. Suppose we're located in Louisiana and we want a default value of 'LA' for state since that's where our clients are usually located. We would enter the following to set the default:

Notice that the second line starts with ALTER and not CHANGE. If we change our mind about having a default value for state, we would enter the following to reset it back to NULL (or whatever the initial default value would be based on the data type):

This particular DROP doesn't delete data, by the way.

Indexes

One of the most irritating tasks in making changes to a table for newcomers is dealing with indexes. If they try to rename a column that is indexed by only using an statement like we used earlier, they will get a frustrating and confusing error message:

If they're typing this column change from memory, they will wear themselves out trying different deviations thinking that they remembered the syntax wrong. What most newcomers to MariaDB don't seem to realize is that the index is separate from the indexed column. To illustrate, let's take a look at the index for clients by using the statement:

The text above shows that behind the scenes there is an index associated with cust_id. The column cust_id is not the index. Incidentally, the G at the end of the statement is to display the results in portrait instead of landscape format. Before the name of an indexed column can be changed, the index related to it must be eliminated. The index is not automatically changed or deleted. Therefore, in the example above, MariaDB thinks that the developer is trying to create another primary key index. So, a DROP clause for the index must be entered first and then a CHANGE for the column name can be made along with the establishing of a new index:

The order of these clauses is necessary. The index must be dropped before the column can be renamed. The syntax here is for a PRIMARY KEY. There are other types of indexes, of course. To change a column that has an index type other than a PRIMARY KEY. Assuming for a moment that cust_id has a UNIQUE index, this is what we would enter to change its name:

Although the index type can be changed easily, MariaDB won't permit you to do so when there are duplicate rows of data and when going from an index that allows duplicates (e.g., INDEX) to one that doesn't (e.g., UNIQUE). If you actually do want to eliminate the duplicates, though, you can add the IGNORE flag to force the duplicates to be deleted:

In this example, we're not only changing the indexed column's name, but we're also changing the index type from INDEX to UNIQUE. And, again, the IGNORE flag tells MariaDB to ignore any records with duplicate values for cust_id.

Renaming & Shifting Tables

The previous sections covered how to make changes to columns in a table. Sometimes you may want to rename a table. To change the name of the clients table to client_addresses we enter this:

The RENAME TABLE statement will also allows a table to be moved to another database just by adding the receiving database's name in front of the new table name, separated by a dot. Of course, you can move a table without renaming it. To move the newly named client_addresses table to the database db2, we enter this:

Finally, with tables that contain data (excluding tables), occasionally it's desirable to resort the data within the table. Although the clause in a statement can do this on the fly as needed, sometimes developers want to do this somewhat permanently to the data within the table based on a particular column or columns. It can be done by entering the following:

Notice that we're sorting by the city first and then by the client's name. Now when the developer enters a statement without an clause, the results are already ordered by the default of city and then name, at least until more data is added to the table.

This is not applicable to tables, the default, which are ordered according to the clustered index, unless the primary key is defined on the specific columns.

Summation

Good planning is certainly important in developing a MariaDB database. However, as you can see, MariaDB is malleable enough that it can be reshaped without much trouble. Just be sure to make a backup before restructuring a table and be sure to check your work and the data when you're finished. With all of this in mind, you should feel comfortable in creating tables since they don't have to be perfect from the beginning.

_{This page is licensed: CC BY-SA / Gnu FDL}

A Tutorial Introduction

Up-front warning: This is the beginning of a very basic tutorial on views, based on my experimentation with them. This tutorial assumes that you've read the appropriate tutorials up to and including More Advanced Joins (or that you understand the concepts behind them). This page is intended to give you a general idea of how views work and what they do, as well as some examples of when you could use them.

Requirements for This Tutorial

In order to perform the SQL statements in this tutorial, you will need access to a MariaDB database and you will need the CREATE TABLE and CREATE VIEW privileges on this table.

The Employee Database

First, we need some data we can perform our optimizations on, so we'll recreate the tables from the tutorial, to provide us with a starting point. If you have already completed that tutorial and have this database already, you can skip ahead.

First, we create the table that will hold all of the employees and their contact information:

Next, we add a few employees to the table:

Now, we create a second table, containing the hours which each employee clocked in and out during the week:

Finally, although it is a lot of information, we add a full week of hours for each of the employees into the second table that we created:

Working with the Employee Database

In this example, we are going to assist Human Resources by simplifying the queries that their applications need to perform. At the same time, it's going to enable us to abstract their queries from the database, which allows us more flexibility in maintaining it.

Filtering by Name, Date and Time

In the previous tutorial, we looked at a JOIN query that displayed all of the lateness instances for a particular employee. In this tutorial, we are going to abstract that query somewhat to provide us with all lateness occurrences for all employees, and then standardize that query by making it into a view.

Our previous query looked like this:

The result:

Refining Our Query

The previous example displays to us all of Heimholtz's punch-in times that were after seven AM. We can see here that Heimholz has been late twice within this reporting period, and we can also see that in both instances, he either left exactly on time or he left early. Our company policy, however, dictates that late instances must be made up at the end of one's shift, so we want to exclude from our report anyone whose clock-out time was greater than 10 hours and one minute after their clock-in time.

This gives us the following list of people who have violated our attendance policy:

The Utility of Views

We can see in the previous example that there have been several instances of employees coming in late and leaving early. Unfortunately, we can also see that this query is getting needlessly complex. Having all of this SQL in our application not only creates more complex application code, but also means that if we ever change the structure of this table we're going to have to change what is becoming a somewhat messy query. This is where views begin to show their usefulness.

Creating the Employee Tardiness View

Creating a view is almost exactly the same as creating a SELECT statement, so we can use our previous SELECT statement in the creation of our new view:

Note that the first line of our query contains the statement 'SQL SECURITY INVOKER' - this means that when the view is accessed, it runs with the same privileges that the person accessing the view has. Thus, if someone without access to our Employees table tries to access this view, they will get an error.

Other than the security parameter, the rest of the query is fairly self explanatory. We simply run 'CREATE VIEW AS' and then append any valid SELECT statement, and our view is created. Now if we do a SELECT from the view, we can see we get the same results as before, with much less SQL:

Now we can even perform operations on the table, such as limiting our results to just those with a Difference of at least five minutes:

Other Uses of Views

Aside from just simplifying our application's SQL queries, there are also other benefits that views can provide, some of which are only possible by using views.

Restricting Data Access

For example, even though our Employees database contains fields for Position, Home Address, and Home Phone, our query does not allow for these fields to be shown. This means that in the case of a security issue in the application (for example, an SQL injection attack, or even a malicious programmer), there is no risk of disclosing an employee's personal information.

Row-level Security

We can also define separate views to include a specific WHERE clause for security; for example, if we wanted to restrict a department head's access to only the staff that report to him, we could specify his identity in the view's CREATE statement, and he would then be unable to see any other department's employees, despite them all being in the same table. If this view is writeable and it is defined with the CASCADE clause, this restriction will also apply to writes. This is actually the only way to implement row-level security in MariaDB, so views play an important part in that area as well.

Pre-emptive Optimization

We can also define our views in such a way as to force the use of indexes, so that other, less-experienced developers don't run the risk of running un-optimized queries or JOINs that result in full-table scans and extended locks. Expensive queries, queries that SELECT *, and poorly thought-out JOINs can not only slow down the database entirely, but can cause inserts to fail, clients to time out, and reports to error out. By creating a view that is already optimized and letting users perform their queries on that, you can ensure that they won't cause a significant performance hit unnecessarily.

Abstracting Tables

When we re-engineer our application, we sometimes need to change the database to optimize or accommodate new or removed features. We may, for example, want to our tables when they start getting too large and queries start taking too long. Alternately, we may be installing a new application with different requirements alongside a legacy application. Unfortunately, database redesign will tend to break backwards-compatibility with previous applications, which can cause obvious problems.

Using views, we can change the format of the underlying tables while still presenting the same table format to the legacy application. Thus, an application which demands username, hostname, and access time in string format can access the same data as an application which requires firstname, lastname, user@host, and access time in Unix timestamp format.

Summary

Views are an SQL feature that can provide a lot of versatility in larger applications, and can even simplify smaller applications further. Just as stored procedures can help us abstract out our database logic, views can simplify the way we access data in the database, and can help un-complicate our queries to make application debugging easier and more efficient.

The initial version of this article was copied, with permission, from on 2012-10-05.

_{This page is licensed: CC BY-SA / Gnu FDL}

With MariaDB it's very easy to copy tables between different MariaDB databases and different MariaDB servers. This works for tables created with the Archive, Aria, CSV, InnoDB, MyISAM, MERGE, and XtraDB engines.

The normal procedures to copy a table is:

The table files can be found in datadir/databasename (you can executeSELECT @@datadir to find the correct directory). When copying the files, you should copy all files with the same table_name + various extensions. For example, for an Aria table of name foo, you will have files foo.frm, foo.MAI, foo.MAD and possibly foo.TRG if you have triggers.

If one wants to distribute a table to a user that doesn't need write access to the table and one wants to minimize the storage size of the table, the recommended engine to use is Aria or MyISAM as one can pack the table with aria_pack or myisampack respectively to make it notablly smaller. MyISAM is the most portable format as it's not dependent on whether the server settings are different. Aria and InnoDB require the same block size on both servers.

Copying Tables When the MariaDB Server is Down

The following storage engines support export without FLUSH TABLES ... FOR EXPORT, assuming the source server is down and the receiving server is not accessing the files during the copy.

Copying Tables Live From a Running MariaDB Server

For all of the above storage engines (Archive, Aria, CSV, MyISAM and MERGE), one can copy tables even from a live server under the following circumstances:

• You have done a FLUSH TABLES or FLUSH TABLE table_name for the specific table.

• The server is not accessing the tables during the copy process.

The advantage of is that the table is read locked until is executed.

Warning: If you do the above live copy, you are doing this on your own risk as if you do something wrong, the copied table is very likely to be corrupted. The original table will of course be fine.

An Efficient Way to Give Someone Else Access to a Read Only Table

If you want to give a user access to some data in a table for the user to use in their MariaDB server, you can do the following:

First let's create the table we want to export. To speed up things, we create this without any indexes. We use TRANSACTIONAL=0 ROW_FORMAT=DYNAMIC for Aria to use the smallest possible row format.

Then we pack it and generate the indexes. We use a big sort buffer to speed up generating the index.

The procedure for MyISAM tables is identical, except that doesn't have the --ignore-control-file option.

Copying InnoDB's Transportable Tablespaces

InnoDB's file-per-table tablespaces are transportable, which means that you can copy a file-per-table tablespace from one MariaDB Server to another server. See for more information.

Importing Tables

Tables that use most storage engines are immediately usable when their files are copied to the new .

However, this is not true for tables that use . InnoDB tables have to be imported with . See for more information.

See Also

• - Compressing the MyISAM data file for easier distribution.

• - Compressing the Aria data file for easier distribution

_{This page is licensed: CC BY-SA / Gnu FDL}

For a basic overview, see .

There are four main kinds of indexes; primary keys (unique and not null), unique indexes (unique and can be null), plain indexes (not necessarily unique) and full-text indexes (for full-text searching).

The terms 'KEY' and 'INDEX' are generally used interchangeably, and statements should work with either keyword.

Primary Key

A primary key is unique and can never be null. It will always identify only one record, and each record must be represented. Each table can only have one primary key.

In tables, all indexes contain the primary key as a suffix. Thus, when using this storage engine, keeping the primary key as small as possible is particularly important. If a primary key does not exist and there are no UNIQUE indexes, InnoDB creates a 6-bytes clustered index which is invisible to the user.