Category: MySQL

MySQL provides an easy way to export table result set into a CSV file. Suppose we have a query like: SELECT name, email, age FROM users WHERE status = ‘Active’; So if we need to export this data into a csv file then using below query we can do: SELECT name, email, age FROM users […]

First of all think whether you require a NULL field or not.If having an empty string value can make any sense then use it otherwise use NULL value for a specific reason. So, Put NOT NULL if required. NULL columns require additional space in the row and add complexity to your statement. Avoid it for […]

If you want to analyze the columns structures of a MySQL table and want to view the actual data in your table with certain suggestion then you can get help from PROCEDURE ANALYSE(). For example, if any data present in your table and plays a big role in any decision making then this is very […]

Based on patterns regular expression is a powerful tool which gives you a flexible way to identify strings like email, IP address, phone number etc. Use Regular Expression in Mysql: For example, you want to find a name which starts with character A, B and C. SELECT name FROM users WHERE name REGEXP ‘^(A|B|C)’; Must […]

You can show the null values which represent the concept of missing or inapplicable information, with other values for better data representation. Suppose we have a table as below: Name Country John India Jane Australia Tim [NULL] So if you query from the database the null result will show the blank result. You can replace […]

It is very useful to copy table data from a old existing table to a new table when you need to back up data and replicating the production data for testing. To copy data from one table to another table, you can use CREATE TABLE and SELECT statements as follows. Simple example of copy table: […]

Using CREATE TRIGGER statement you can create a trigger. You have to put the trigger name after the trigger statement. Let’s see an example of Create Trigger: For example we have two tables i.e. users and userhistory. users:userIDfirstNamelastName userhistory:userIDlast_update Read Also: INSERT … ON DUPLICATE KEY UPDATE Now we will create a trigger before update […]

You can add a sequential number as a custom row number of a row using SET clause and increment at next row. Suppose users table has columns name and age.When you want to show the result you want to add extra column serial_no. So using below query you can achieve custom row number: SET @serial_number = […]

MySQL triggers are nothing but a SQL statement stored in the database. When any event associate with a table is fired i.e. insert, update or delete a trigger is executed. TRUNCATE statement delete the all data in a table but does not execute the triggers associated with this. The different between trigger and stored procedure […]

One of the important thing for achieving MySQL database performance is indexing that allows faster gathering of data to optimize MySQL database. Here is the list to Optimize MySQL database: Indexing in MySQL will create an internal register which is saved in by the MySQL service.For e.g.ALTER TABLE table_name ADD INDEX (column_name); There is another […]