Triggers in MySQL

Posted by phpbits | MySql | Sunday 31 August 2008 6:30 am

MySQL is indeed the most powerful database management system and the reason for this lies in a number of tools integrated with it. Triggers in MySQL are the most significant methods to automate the updating of one table in the database, corresponding to any type of change brought in other table. Triggers are fastest way to reduce the probability of inconsistent data occurring in database, without demanding the interference of database user. Here is the fact list regarding triggers in MySQL, which may help you to understand these codes in an accurate manner:

  • Triggers in MySQL reside on the server side and thus, consumes less time to get activated and perform the task for which these are created.
  • These triggers are activated in response to SQL queries submitted to the database. Thus, you don’t need to follow any specific procedure to trigger them.
  • Insert, Update and Delete are the queries employed on a particular table, which encourage the triggers applied on the table to arouse and do the needed function.

How To Create Triggers

The triggers in MySQL are created using a simple syntax, as mentioned below:

  1. CREATE TRIGGER triggername1 AFTER INSERT ONtablename1 FOR EACH ROW UPDATE tablename2 perform function;

Here is the explanation of the above code:

  1. CREATE TRIGGER statement is used to create a trigger on table (tablename1).
  2. AFTER INSERT defines the SQL query which must be used to activate this particular trigger. You may also use AFTER DELETE syntax to set a trigger on work.
  3. UPDATE syntax is used to bring in the modifications in another table (tablename2).
  4. Finally a particular function is assigned to the trigger, which will be performed by it, once it gets activated in the above statement.

SHOW TRIGGER statement is used to list all active triggers finding space on the server side of your database. This will also provide information about various attributes and statements associated with different triggers.

The triggers in MySQL can’t be changed simply using ALTER statement or any other procedure. Thus, one has to DROP the existing trigger and then CREATE a new one, specifying the expected modifications.

A Simple Example
Suppose you have two tables named sell and inventory in your database. The sell table is used to keep the record of sales made in a day and table inventory maintains the record of total quantity of each product in the store. Now, corresponding to each sale, the inventory table must be updated to decrease the count of a product on each successful sale. Thus, one may define a trigger to update inventory, with each sale record updated in table sell.

Advantages Of Using Triggers

Triggers in MySQL exhibit a number of advantages to the users and thus, have caught popularity amongst database users. Here are most prominent advantages offered by triggers:

  1. Triggers help in maintaining the integrity of database and thus, there is no scope of wrong information stored in the data tables.
  2. Triggers don’t require the user to add separate application code for activation and thus, it saves the efforts of database designer.
  3. Triggers are easy to activate and most importantly, consume less time in this process.
  4. Triggers are easy to get ported from one platform to another, along with complete module of the database.

Stored Procedures in MySQL

Posted by phpbits | MySql | Thursday 26 June 2008 10:52 am

The programmers and developers all over the world are celebrating the existence of MySQL 5.0 for a good reason. The version has finally paved the way for using stored procedures with this popular open source database. The stored procedures are hoarded at database server in the form of commands and SQL statements and could be invoked for any type of application. Thus, there is advantage for programmers to shed away the need of creating their own SQL; a tiresome job.

Without taking much of the time, let us understand the syntax for utilizing the procedures:

  1.  
  2. CREATE ProcedureCREATE    [DEFINER = { user | CURRENT_USER }]PROCEDURE sp_name ([proc_parameter[,…]])
  3.  
  4. [characteristic …] routine_body
  5.  
  6. IN the above syntax, the parameters are specified AS IN, OUT OR INOUT. However, IN case of CREATE FUNCTION displayed below, the only parameter allowed IS IN.
  7.  
  8. CREATE
  9.  
  10. [DEFINER = { user | CURRENT_USER }]
  11.  
  12. FUNCTION sp_name ([func_parameter[,…]])
  13.  
  14. RETURNS type
  15.  
  16. [characteristic …] routine_body

Return statement is included in the syntax for a function and it specifies the return type of the function. Routine body is included to define a valid SQL statement. The syntax contains characteristic to provide information regarding nature of data. This may include attributes like CONTAINS SQL, NO SQL, READS SQL DATA and MODIFIES SQL DATA.

Alter Procedure

This statement is used to modify or alter the characteristics possessed by a stored procedure. Here is the syntax:

  1.  
  2. ALTER {PROCEDURE | FUNCTION} sp_name [characteristic …]

It offers you the advantage to bring in more than one change in the characteristics of stored procedures.

Drop Procedure

In order to remove a stored procedure, there is provision of DROP statement in MySQL database. The syntax for this takes the following shape:

  1.  
  2. DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

IF EXISTS is used to check the existence of specified procedure or function. It saves the statement from generating an error in case the procedure or function doesn’t exist.

Other Statements For Procedures

Apart from those mentioned above, there are other statements used for stored procedures. Here is the list o these statements:

  • Show Create Procedure – It is used to return the text of a stored procedure, which was defined previously using Create syntax. In the similar manner, there is Show Create Function statement to accomplish this task for stored function.
  • Show Procedure Status – It is used to return the characteristic of the specified stored procedure. These characteristics include name, type, creation date and so on. You may use Show Function Status also.
  • CALL – It is used to invoke the stored procedure created using Create Procedure statement.
  • Begin End – It is used to specify multiple statements for carrying out different types of operations and executions.
  • Declare – It is used to help programmers in declaring and defining local variables, conditions, procedures and so on.
  • SET – This statement is used to modify the values of local as well as global variables.

Also, there are condition statements like IF THEN ELSE, WHILE, CASE WHEN amongst others, which could be employed to operate these procedures.

Thus, MySQL stored procedures are tools for better performance, secure environment, providing ease of maintenance and also, for optimization.

MySQL Full-text Search Function

Posted by phpbits | MySql | Monday 2 June 2008 5:31 am

MySQL 4.0 has come up with a completely functional method to employ text searches on the table columns. In fact, the method known as full-text search function is capable of building a small search engine to conduct searches on CHAR, VARCHAR and TEXT columns. Though the function was added in the MySQL 3.23.23 version, but full capabilities of this function could be explored in MySQL 4.0.

Full-Text Search Defined

Suppose you want to create a table with few text fields like first name, last name and profile. You can provide space to mysql full-text functionality in the following manner:

  1.  
  2. CREATE TABLE person (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,firstName VARCHAR(200),lastName VARCHAR(200),profile TEXT,FULLTEXT (firstName, lastName, profile)) Engine=MyIsam;

Alternatively, if you already have a table and want to add full-text functionality to it, you may follow a similar code as given below:

  1.  
  2. ALTER TABLE person ADD FULLTEXT (firstName, lastName, profile);

These lines help the MySQL to set up an index on text fields of firstName, lastName and profile. This enhances the capability of MySQL to refine its search criterions, whenever a user tries to search for textual data on a website.

After successful implementation of Full Text is done, one may follow the following syntax to carry out the search:

  1.  
  2. SELECT col1, col2 FROM tableWHERE MATCH (col1, col2) AGAINST (‘expression’);

The MATCH function specifies the name of the columns, for which a particular search is carried out. The AGAINST function is used to specify the value of the query to be searched. One may specify search modifier in this function to carry out Boolean Mode search and also, to use Query Expansion.

Example:
Let us assume that the above table contains the following data:

id firstName lastName profile
1 MySQL mysql MySQL is an open source RDBMS.
2 PHP Hypertext Preprocessor PHP is an open source server side programming language.
3 JavaScript js JavaScript is a client side scripting using this we can validate form data & can create lots of dynamic contents on the web page.

Run the following query to search the word ‘mysql’

  1.  
  2. SELECT *,MATCH(`firstName`,`profile`,`lastName`) AGAINST(‘mysql’) AS rel FROM `person` ORDER BY rel DESC

The output will be like this:

id firstName lastName profile rel
1 MySQL mysql MySQL is an open source RDBMS. 1.091023247894
2 PHP Hypertext Preprocessor PHP is an open source server side programming language. 0
3 JavaScript js JavaScript is a client side scripting using this we can validate form data & can create lots of dynamic contents on the web page. 0

Usage of Full-Text Function

Full Text function could be used at four main places:

  • Natural Language Searches, where search is carried out for a particular string against the text collection specified as Match parameters.
  • Boolean Mode Searches, using In Boolean Mode modifier and implied Boolean logic in the form of AND, OR and NOT.
  • Stopwords Full Text to ignore the noise words like THE, HAVE, SOME and so on.
  • Query Expansion Full Text search to automatically expand the range of words to be included in the search criterion. This means that once a search is carried out using a specific word, then the search is repeated and this time other words appearing significantly in the text collection are also included.

Some Points worth Mentioning

Here are few points, which are relevant to the usage of Full Text function:

  • This function employ case-insensitive search criterion.
  • Only MyISAM tables are incorporated in the search carried out by this function.
  • The search is carried out amongst the words with default length of 4 characters or more.
  • One may use ft_min_word_len and ft_max_word_len functions to specify the length of the search words.
  • For large data sets, it is advised to employ Full Text function after loading the data into the table.
  • Full Text search is incapable of working with some natural languages like Chinese and Japanese, which lack word delimiters.

Full Text search is good for large databases, where it is practically impossible to use LIKE and other MySQL methods.