To do this, we'll use the AFTER option: ALTER TABLE clients ADD COLUMN address2 varchar ( 25 ) AFTER address īy 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. An ALTER TABLE statement could be entered like above, but it will look tidier if the new column is located right after the address column. That data could be stored in the address column, but it would better for it to be in a separate column. In looking over the table again, it's decided that another field for client apartment numbers or the like needs to be added. 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). To make this change, the following is entered: ALTER TABLE clients ADD COLUMN status CHAR ( 2 ) 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). In order to add a column to an existing MariaDB table, one would use the ALTER TABLE statement. In the examples in this article, we are assuming that there isn't any data in the tables yet. So be sure that users haven't added data in the interim. 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. It uses the mariadb client from the outside, so to speak. Notice that this line does not use the mariadb-dump utility. If the table should need to be restored, the following can be run from the shell: mariadb -user='username' -password='password' db1 < clients.sql A directory path could be put in front of the file name to create the file elsewhere. 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. ![]() On the third line above, the database name is given, followed by the table name. 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. There are many other options in mariadb-dump that could be used, but for our purposes this one is all that's necessary. ![]() 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. To backup the clients table with mariadb-dump, we will enter the following from the command-line: mariadb-dump -user='username' -password='password' -add-locks db1 clients > clients.sqlĪs you can see, the username and password are given on the first line. Even if your database is on your own server, though, the mariadb-dump utility is typically the best tool for making and restoring backups in MariaDB, and it's generally permitted by web hosting companies. There are a few ways to do this, but some choices may not be permitted by your web hosting company. 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. However, it's sufficient for the examples here in which we will change several of its columns. This is a very simple table that will hold very little information. To start off, we'll enter a DESCRIBE statement to see what the table looks like: DESCRIBE clients + -+-+-+-+-+-+ | Field | Type | Null | Key | Default | Extra | + -+-+-+-+-+-+ | cust_id | int ( 11 ) | | PRI | 0 | | | name | varchar ( 25 ) | YES | | NULL | | | address | varchar ( 25 ) | YES | | NULL | | | city | varchar ( 25 ) | YES | | NULL | | | state | char ( 2 ) | YES | | NULL | | | zip | varchar ( 10 ) | YES | | NULL | | | client_type | varchar ( 4 ) | YES | | NULL | | + -+-+-+-+-+-+ The clients table is for keeping track of client names and addresses. Before Beginningįor the examples in this article, we will refer to a database called db1 containing a table called clients. In this article we'll explore the ways to alter tables in MariaDB and we'll give some precautions about related potential data problems. And some changes can be very frustrating. This is not very difficult, but some developers are unfamiliar with the syntax for the functions used in MariaDB to accomplish this. Despite a MariaDB developer's best planning, occasionally one needs to change the structure or other aspects of tables.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |