Found 4219 Articles for MySQLi

Suppose if an error occurs during trigger execution then MySQL can handle it as follows −If a BEFORE trigger fails, the operation on the corresponding row is not performed.A BEFORE trigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds.An AFTER trigger is executed only if any BEFORE triggers and the row operation execute successfully.An error during either a BEFORE or AFTER trigger results in failure of the entire statement that caused trigger invocation.For transactional tables, failure of a statement should cause a rollback of all changes performed by the statement. ... Read More

As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car (number char(9)); Query OK, 0 rows affected (0.32 sec) mysql> Insert into car values('AB-235-YZ'); Query OK, 1 row affected (0.10 sec)The above value is a valid one but what ... Read More

Actually, MySQL SIGNAL statement is an error handling mechanism for handling unexpected occurrences and a graceful exit from the application if need to be. Basically, it provides error information to the handler. Its basic syntax would be as follows −SIGNAL SQLSTATE | condition_value [SET signal_information_item = value_1, [, signal_information_item] = value_2, etc;]Here, the SIGNAL keyword is an SQLSTATE value or a condition name declared by a DECLARE CONDITION statement. The SIGNAL statement must always specify an SQLSTATE value or a named condition that defined with an SQLSTATE value. The SQLSTATE value for a The SIGNAL statement consists of a five-character ... Read More

We can change the name of a particular existing column from a MySQL table by using CHANGE statement along with ALTER statement. Its syntax would be as follows −SyntaxALTER TABLE table_name CHANGE old_column_name new_column_name datatype;Here,  table_name is the name of the table from which we want to delete the column.Old_column_name is the name of the column which is to be changed.new_column_name is the name of the column which has to be given to the old column.ExampleIn this example, we are changing the name of the column ‘id’ to ‘studentid’ from table ‘student_info’ as follows −mysql> Select * from Student_info; +------+---------+------------+------------+ | id   | ... Read More

We can delete a particular existing column from a MySQL table by using the DROP statement along with an ALTER statement. Its syntax would be as follows −SyntaxALTER TABLE table_name DROP column_name;Here,  table_name is the name of the table from which we want to delete the column.Column_name is the name of the column which is to be deleted from the table.ExampleIn this example, we are deleting the column ‘address’ from table ‘student_detail’ as follows −mysql> select * from student_detail; +-----------+-------------+----------+ | Studentid | StudentName | address  | +-----------+-------------+----------+ |       100 | Gaurav      | Delhi   ... Read More

Since we cannot use the MINUS query in MySQL, we will use JOIN to simulate the MINUS query. It can be understood with the help of the following example −ExampleIn this example, we are two tables namely Student_detail and Student_info having the following data −mysql> Select * from Student_detail; +-----------+---------+------------+------------+ | studentid | Name    | Address    | Subject    | +-----------+---------+------------+------------+ |       101 | YashPal | Amritsar   | History    | |       105 | Gaurav  | Chandigarh | Literature | |       130 | Ram     | Jhansi ... Read More

With the help of SHOW STATUS statement, we can get the count of MySQL event-related operations. It can be used as follows −mysql> SHOW STATUS LIKE '%event%'; +--------------------------+-------+ | Variable_name            | Value | +--------------------------+-------+ | Com_alter_event          | 16    | | Com_create_event         | 6     | | Com_drop_event           | 4     | | Com_show_binlog_events   | 0     | | Com_show_create_event    | 0     | | Com_show_events          | 4     | | Com_show_relaylog_events | 0     | +--------------------------+-------+ 7 rows in set (0.17 sec)

Followings are the status variables in MYSQL which provide us the counts of event-related operations −Com_create_event It provides us the number of CREATE EVENT statements executed since the last server restart.Com_alter_event − It provides us the number of ALTER EVENT statements executed since the last server restart.Com_drop_event − It provides us the number of DROP EVENT statements executed since the last server restart.Com_show_create_event − It provides us the number of SHOW CREATE EVENT statements executed since the last server restart.Com_show_events − It provides us the number of SHOW EVENTS statements executed since the last server restart.Read More

It can be done with the help of the INFORMATION_SCHEMA database. The following statement will give us the metadata of events −mysql> SELECT * from INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME LIKE '%event%' A ND EVENT_SCHEMA = 'query'\G *************************** 1. row ***************************       EVENT_CATALOG: def        EVENT_SCHEMA: query          EVENT_NAME: testing_event6             DEFINER: root@localhost           TIME_ZONE: SYSTEM          EVENT_BODY: SQL    EVENT_DEFINITION: INSERT INTO event_message(message, generated_at) values('EVENT ALTERED', NOW())          EVENT_TYPE: ONE TIME          EXECUTE_AT: 2017-11-22 20:03:52 ... Read More

It can be done with the help of ALTER EVENT statement too. We need to use the combination of database name and event name along with the RENAME keyword. To illustrate it we are having the following example in which we are moving the event named ‘hello_renamed’ from ‘query’ database to ‘tutorial’ database −Examplemysql> ALTER EVENT query.hello_renamed RENAME to tutorials.hello_renamed; Query OK, 0 rows affected (0.00 sec)To confirm that event has been moved to database ‘tutorials’ we can try to delete the event with an old name, MySQL will throw an error as follows −mysql> DROP event hello_renamed; ERROR 1539 (HY000): Unknown ... Read More