Wednesday, 21 May 2014

MySQL Transactions And MySQL ALTER Command

Post By: Hanan Mannan
Contact Number: Pak (+92)-321-59-95-634
-------------------------------------------------------

MySQL Transactions

A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

Practically, you will club many SQL queries into a group and you will execute all of them together as part of a transaction.

Properties of Transactions:

Transactions have the following four standard properties, usually referred to by the acronym ACID:

Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.
Consistency: ensures that the database properly changes states upon a successfully committed transaction.
Isolation: enables transactions to operate independently on and transparent to each other.
Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

In MySQL, transactions begin with the statement BEGIN WORK and end with either a COMMIT or a ROLLBACK statement. The SQL commands between the beginning and ending statements form the bulk of the transaction.

COMMIT and ROLLBACK:

These two keywords Commit and Rollback are mainly used for MySQL Transactions.

When a successful transaction is completed, the COMMIT command should be issued so that the changes to all involved tables will take effect.
If a failure occurs, a ROLLBACK command should be issued to return every table referenced in the transaction to its previous state.

You can control the behavior of a transaction by setting session variable called AUTOCOMMIT. If AUTOCOMMIT is set to 1 (the default), then each SQL statement (within a transaction or not) is considered a complete transaction and committed by default when it finishes. When AUTOCOMMIT is set to 0, by issuing the SET AUTOCOMMIT=0 command, the subsequent series of statements acts like a transaction and no activities are committed until an explicit COMMIT statement is issued.

You can execute these SQL commands in PHP by using mysql_query() function.

Generic Example on Transaction

This sequence of events is independent of the programming language used; the logical path can be created in whichever language you use to create your application.

You can execute these SQL commands in PHP by using mysql_query() function.

Begin transaction by issuing SQL command BEGIN WORK.
Issue one or more SQL commands like SELECT, INSERT, UPDATE or DELETE.
Check if there is no error and everything is according to your requirement.
If there is any error, then issue ROLLBACK command, otherwise issue a COMMIT command.

Transaction-Safe Table Types in MySQL:

You can not use transactions directly, you can but they would not be safe and guaranteed. If you plan to use transactions in your MySQL programming, then you need to create your tables in a special way. There are many types of tables, which support transactions but most popular one is InnoDB.

Support for InnoDB tables requires a specific compilation parameter when compiling MySQL from source. If your MySQL version does not have InnoDB support, ask your Internet Service Provider to build a version of MySQL with support for InnoDB table types or download and install the MySQL-Max binary distribution for Windows or Linux/UNIX and work with the table type in a development environment.

If your MySQL installation supports InnoDB tables, simply add a TYPE=InnoDB definition to the table creation statement. For example, the following code creates an InnoDB table called tcount_tbl:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> create table tcount_tbl
    -> (
    -> tutorial_author varchar(40) NOT NULL,
    -> tutorial_count  INT
    -> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

Check the following link to know more about: InnoDB

You can use other table types like GEMINI or BDB, but it depends on your installation if it supports these two types.

MySQL ALTER Command

MySQL ALTER command is very useful when you want to change a name of your table, any table field or if you want to add or delete an existing column in a table.

Let's begin with creation of a table called testalter_tbl.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use HANANMANNAN;
Database changed
mysql> create table testalter_tbl
    -> (
    -> i INT,
    -> c CHAR(1)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Dropping, Adding or Repositioning a Column:

Suppose you want to drop an existing column i from above MySQL table then you will use DROP clause along with ALTER command as follows:

mysql> ALTER TABLE testalter_tbl  DROP i;

A DROP will not work if the column is the only one left in the table.

To add a column, use ADD and specify the column definition. The following statement restores the icolumn to testalter_tbl:

mysql> ALTER TABLE testalter_tbl ADD i INT;

After issuing this statement, testalter will contain the same two columns that it had when you first created the table, but will not have quite the same structure. That's because new columns are added to the end of the table by default. So even though i originally was the first column in mytbl, now it is the last one.

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

To indicate that you want a column at a specific position within the table, either use FIRST to make it the first column or AFTER col_name to indicate that the new column should be placed after col_name. Try the following ALTER TABLE statements, using SHOW COLUMNS after each one to see what effect each one has:

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position.

Changing a Column Definition or Name:

To change a column's definition, use MODIFY or CHANGE clause along with ALTER command. For example, to change column c from CHAR(1) to CHAR(10), do this:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

With CHANGE, the syntax is a bit different. After the CHANGE keyword, you name the column you want to change, then specify the new definition, which includes the new name. Try out the following example:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

If you now use CHANGE to convert j from BIGINT back to INT without changing the column name, the statement will be as expected:

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

The Effect of ALTER TABLE on Null and Default Value Attributes:

When you MODIFY or CHANGE a column, you can also specify whether or not the column can contain NULL values and what its default value is. In fact, if you don't do this, MySQL automatically assigns values for these attributes.

Here is the example, where NOT NULL column will have value 100 by default.

mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

If you don't use above command, then MySQL will fill up NULL values in all the columns.

Changing a Column's Default Value:

You can change a default value for any column using ALTER command. Try out the following example.

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

You can remove default constraint from any column by using DROP clause along with ALTER command.

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Changing a Table Type:

You can use a table type by using TYPE clause along with ALTER command. Try out the following example to change testalter_tbl to MYISAM table type.

To find out the current type of a table, use the SHOW TABLE STATUS statement.

mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G
*************************** 1. row ****************
           Name: testalter_tbl
           Type: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 25769803775
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-06-03 08:04:36
    Update_time: 2007-06-03 08:04:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Renaming a Table:

To rename a table, use the RENAME option of the ALTER TABLE statement. Try out the following example to rename testalter_tbl to alter_tbl.

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

You can use ALTER command to create and drop INDEX on a MySQL file. We will see this feature in next chapter.

Wednesday, 21 May 2014