In the following section, we have given a list of FUNCTIONS and OPERATORS in MySQL as anyone can view the FUNCTIONS at a glance and jump immediately to his/her requirement.
MySQL logical AND operator compares two expressions and returns true if both of the expressions are true. Some Logical Operators are – And operatorNot operatorOr operatorXor operator
MySQL Control Flow Functions
Some Control Flow Functions are – Case operatorIF()IFNULL()NULLIF()
Some Information Functions in MySQL are – BENCHMARK()CHARSET()COERCIBILITY()COLLATION()CONNECTION_ID()CURRENT_USER(), CURRENT_USERDATABASE()FOUND_ROWS()LAST_INSERT_ID()SCHEMA()SESSION_USER()SYSTEM_USER()USER()VERSION()
In MySQL, the UNION operator is used to combine the result from multiple SELECT statements into a single result set.
The default characteristic of UNION is, to remove the duplicate rows from the result. The DISTINCT keyword which is optional does not make any effect, because, by default, it specifies duplicate-row removal. But if we use the optional keyword ALL, the duplicate-row removal does not happen and the result set includes all matching rows from all the SELECT statements.
Here in the above picture shows, only the employee_id and job_id surrounded the red rectangle are same in employees and job_history table, so it comes once in the output but the other employee_id and job_id are different in both the tables, so they come each.
MySQL UNION ALL
The UNION ALL operator does not eliminate duplicate selected rows and returns all rows.
Pictorial presentation of UNION ALL operator
The UNION ALL operator returns all the rows from both the queries and no duplication elimination happens.
MySQL UNION vs UNION ALL
In MySQL the UNION operator returns the combined result from multiple SELECT statements into a single result set but exclude the duplicate rows where as the UNION ALL operator avoids the elimination of duplicate selected rows and returns all rows.
See the example below.
Example
If we want to display the present and previous details of jobs of all employees, and they may appear more than once, the following MySQL statement can be used.
Select employee_id, job_id,department_id
FROM employees
UNION ALL
Select employee_id,job_id,department_id
FROM job_history;
Here in the above picture shows, only the employee_id and job_id surrounded the red rectangle are same in employees and job_history table and they all have appeared in the result set. Here UNION ALL have to avoid the elimination of duplicate rows.
MySQL UNION DISTINCTThe DISTINCT clause with UNION produced nothing extra as the simple UNION done. From the UNION operator, we know that all rows will be displayed from both the queries except the duplicate are once.
Example
If we want to display the present and previous details of jobs of all employees once the following MySQL statement can be used.
SELECT DISTINCT employee_id, job_id
FROM employees
UNION
SELECT DISTINCT employee_id,job_id
FROM job_history;
Copy
Output :
The result will be same as the UNION operator do.
MySQL UNION ORDER BYThe ORDER BY clause with UNION arrange the rows in the result set in a specific order. The default order is ascending. The ORDER BY only used at the very end of the statement.
Example
If we want to display the present and previous details of jobs of all employees once the following MySQL statement can be used.
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id,job_id
FROM job_history
ORDER BY employee_id;
SELECT statement is used to fetch rows or records from one or more tables.
* , ALL
Indicating all columns.
column
Columns or list of columns.
table
Indicates the name of the table from where the rows will be retrieved.
DISTINCT
DISTINCT clause is used to retrieve unique rows from a table.
DISTINCTROW
DISTINCTROW is a synonym for DISTINCT.
HIGH_PRIORITY
If used with SELECT, the associated query obtains higher priority than an update statement. It runs even if the table on which the query is applied is locked by an update query. It can be used only with MyISAM, MEMORY, and MERGE storage engines. And can be used only if the associated query is very fast as well as done at once. If a SELECT statements are part of a UNION, you can not use HIGH_PRIORITY along with.
STRAIGHT_JOIN
If used with SELECT, associated tables are joined in the order they are arranged in the corresponding FROM clause. This can not be used to make a query fast and also can be used in the table_references list.
SQL_SMALL_RESULT
Can be used with GROUP BY or DISTINCT. It tells the optimizer (a component of DataBase system which determines to the most efficient way of executing a query) that the result set is small, so use fast temporary tables to store the resulting table instead of using sorting.
SQL_BIG_RESULT
Can be used with GROUP BY or DISTINCT. It tells the optimizer (a component of DataBase system which determines to the most efficient way of executing a query) that the result set has many rows, so use disk-based temporary tables if needed, and prefer sorting to use a temporary table with a key on the GROUP BY elements.
SQL_BUFFER_RESULT
It forces the result to be put into a temporary table. If used, MySQL can free the table locks early and takes less time to send the result set to the client where it may take a long time. It can not be used for subqueries or following UNION.
SQL_CACHE
It tells MySQL to store the result in the query cache (A DataBase system component which stores the text of a SELECT statement along with the corresponding result sent to the client ) if it is cacheable. The value of the query_cache_type system variable is 2 or DEMAND.
SQL_NO_CACHE
It tells MySQL not to store the result in the query cache (A DataBase system component which stores the text of a SELECT statement along with the corresponding result sent to the client ). It can also be used for views if it accompanies a SELECT statement.
SQL_CALC_FOUND_ROWS
It tells MySQL to calculate the number of rows in a result set. While calculating rows in this fashion, LIMIT clause is ignored. The number of rows can then be retrieved with SELECT FOUND_ROWS().
select_expr
An expression.
FROM
This clause is used after SELECT and preceding tables or subqueries.
table_references
Name of the tables used in a SELECT statement.
WHERE
The conditions are supplied after this keyword (in a select statement).
where_condition
Conditions placed after WHERE.
GROUP BY
If used, the results returned from the field name used after GROUP BY clause is grouped together in result set.
col_name
Name of the column or columns or fields.
expr
An expression.
position
Refers to the position of columns beginning with 1.
ASC
If used, results are returned in ascending order.
DESC
If used, results are returned in descending order.
WITH ROLLUP
This modifier can be used with GROUP BY clause. If used, extra rows are added to the summary output.
HAVING
This modifier can be used with GROUP BY clause and aggregate functions. Can not be used with WHERE clause.
ORDER BY
MySQL ORDER BY clause specifies the order in which columns are sorted while retrieving data in a SELECT statement.
LIMIT
It is always followed by one or two numeric arguments. The first argument is the offset of the first row to be returned and the second argument is the maximum number of rows returned in the result set.
PROCEDURE
This clause names a procedure that should process the data in the result set.
INTO OUTFILE
Takes a backup of the associated table in a file specified.
INTO DUMPFILE
If used instead of INTO OUTFILE, only one row is written into the file. Useful if BLOB values are to be stored in a file.
var_name
A variable name to store data temporarily.
FOR UPDATE
If used with a storage engine that uses page or row locks, untill the end of the transaction, rows checked by the query are write-locked.
LOCK IN SHARE MODE
If used, a shared lock is set upon the rows examined by the associated query. So, other transactions can read the examined rows, can not update or delete those rows.
Following clauses are commonly used in SELECT statements:
Each select_expr indicates a column that you want to retrieve. There must be at least one select_expr.
table_references indicates the table or tables from which to retrieve rows.
The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause (i.e without any condition).
In the WHERE expression, you can use any of the functions and operators that MySQL supports, except for aggregate functions.
SELECT can also be used to retrieve rows computed without reference to any table.
For example:mysql> SELECT 5 – 3 ; +——-+ | 5 – 3 | +——-+ | 2 | +——-+ 1 row in set (0.00 sec)
You can specify DUAL as a dummy table name where no tables are referenced :
mysql> SELECT 5 – 3 FROM DUAL; +——-+ | 5 – 3 | +——-+ | 2 | +——-+ 1 row in set (0.00 sec)
DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced.
In general, clauses used must be given in exactly the order shown in the syntax description. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. In the consequent pages of w3resource MySQL tutorial, you will find detail discussion accompanied by examples (often with PHP codes) of the said arguments of the SELECT statement.
MySQL selecting all data
MySQL SELECT statement without any condition retrieves all records from a table. The following SELECT statement will retrieve all data from publisher table.
SELECT *
FROM publisher;
Copy
Table: publisher
Output:mysql> SELECT * FROM publisher; +——–+——————————+———–+———–+—————-+————–+————+ | pub_id | pub_name | pub_city | country | country_office | no_of_branch | estd | +——–+——————————+———–+———–+—————-+————–+————+ | P001 | Jex Max Publication | New York | USA | New York | 15 | 1969-12-25 | | P002 | BPP Publication | Mumbai | India | New Delhi | 10 | 1985-10-01 | | P003 | New Harrold Publication | Adelaide | Australia | Sydney | 6 | 1975-09-05 | | P004 | Ultra Press Inc. | London | UK | London | 8 | 1948-07-10 | | P005 | Mountain Publication | Houstan | USA | Sun Diego | 25 | 1975-01-01 | | P006 | Summer Night Publication | New York | USA | Atlanta | 10 | 1990-12-10 | | P007 | Pieterson Grp. of Publishers | Cambridge | UK | London | 6 | 1950-07-15 | | P008 | Novel Publisher Ltd. | New Delhi | India | Bangalore | 10 | 2000-01-01 | +——–+——————————+———–+———–+—————-+————–+————+ 8 rows in set (0.00 sec)
When a user wants to retrieve some individual rows from a table, a WHERE clause has to be added with the SELECT statement immediately followed by a condition.
Syntax:SELECT * FROM WHERE where_condition
Here * indicates all columns.
Example:
This following SELECT statement will retrieve those particular rows where ‘country’ is the USA.
Code :SELECT * FROM publisher WHERE country=’USA’
Output:> mysql> SELECT * FROM publisher -> WHERE country=’USA’; +——–+————————–+———-+———+—————-+————–+————+ | pub_id | pub_name | pub_city | country | country_office | no_of_branch | estd | +——–+————————–+———-+———+—————-+————–+————+ | P001 | Jex Max Publication | New York | USA | New York | 15 | 1969-12-25 | | P005 | Mountain Publication | Houstan | USA | Sun Diego | 25 | 1975-01-01 | | P006 | Summer Night Publication | New York | USA | Atlanta | 10 | 1990-12-10 | +——–+————————–+———-+———+—————-+————–+————+ 3 rows in set (0.01 sec)
MySQL SELECT specific rows with AND operator
MySQL AND operator is used to combine more than one conditions aiming to fetch records when both of the conditions are satisfied. The following SELECT statement will retrieve those particular rows where country and city of the publisher are ‘USA’ and ‘New York’.
Code:
SELECT *
FROM publisher
WHERE country='USA'
AND pub_city='New York';
Copy
Output:mysql> SELECT * FROM publisher -> WHERE country=’USA’ -> AND pub_city=’New York’; +——–+————————–+———-+———+—————-+————–+————+ | pub_id | pub_name | pub_city | country | country_office | no_of_branch | estd | +——–+————————–+———-+———+—————-+————–+————+ | P001 | Jex Max Publication | New York | USA | New York | 15 | 1969-12-25 | | P006 | Summer Night Publication | New York | USA | Atlanta | 10 | 1990-12-10 | +——–+————————–+———-+———+—————-+————–+————+ 2 rows in set (0.00 sec)
MySQL SELECT specific columns
To retrieve records from specific columns, you need to specify a list of comma separated columns. The following MySQL statement returns the name of the book, author id, and price of the books from book_mast table.
Table: book_mast
Code:
SELECT book_name,aut_id,book_price
FROM book_mast;
Copy
Output:mysql> SELECT book_name,aut_id,book_price -> FROM book_mast; +————————————-+——–+————+ | book_name | aut_id | book_price | +————————————-+——–+————+ | Introduction to Electrodynamics | AUT001 | 85.00 | | Understanding of Steel Construction | AUT002 | 105.50 | | Guide to Networking | AUT003 | 200.00 | | Transfer of Heat and Mass | AUT004 | 250.00 | | Conceptual Physics | AUT005 | 145.00 | | Fundamentals of Heat | AUT006 | 112.00 | | Advanced 3d Graphics | AUT007 | 56.00 | | Human Anatomy | AUT008 | 50.50 | | Mental Health Nursing | AUT009 | 145.00 | | Fundamentals of Thermodynamics | AUT010 | 225.00 | | The Experimental Analysis of Cat | AUT011 | 95.00 | | The Nature of World | AUT005 | 88.00 | | Environment a Sustainable Future | AUT012 | 100.00 | | Concepts in Health | AUT013 | 180.00 | | Anatomy & Physiology | AUT014 | 135.00 | | Networks and Telecommunications | AUT015 | 45.00 | +————————————-+——–+————+ 16 rows in set (0.01 sec)
MySQL select specific columns with distinct operator
DISTINCT clause is used to retrieve unique rows from a table. The following MySQL statement retrieves the unique author ids from book_mast table.
Note : If you look at the book_mast table shown under Sample table : book_mast heading, you would find that AUT005 (in ‘aut_id’ column) has appeared more than once. Using DISTINCT clause, we got rid of this redundancy.
MySQL SELECT specific columns with logical OR operator
OR operator retrieves records from a table if at least one of the given conditions is satisfied. The following MySQL statement retrieves records of pub_name, country, pub_city columns from publisher table if either Country (i.e. country) of the publisher is ‘USA’ or his city (i.e. pub_city) is ‘New York’.
Code:
SELECT pub_name, country,pub_city
FROM publisher
WHERE country='USA' OR pub_city='New York';
Copy
Output:mysql> SELECT pub_name,country,pub_city -> FROM publisher -> WHERE country=’USA’ OR pub_city=’New York’; +————————–+———+———-+ | pub_name | country | pub_city | +————————–+———+———-+ | Jex Max Publication | USA | New York | | Mountain Publication | USA | Houstan | | Summer Night Publication | USA | New York | +————————–+———+———-+ 3 rows in set (0.00 sec)
MySQL sorting rows in ascending order
MySQL ORDER BY clause specifies the order in which columns are sorted while retrieving data in a SELECT statement. By default, columns are sorted in ascending order. You can use ASC keyword to achieve the same result.
Note : In the case of character type column sorting the sorting is dependent upon case sensitivity. The default sort order is ascending this means smallest value comes first. To sort in reverse order, DESC key has to be used.
In the following MySQL statement, all records of pub_name, country and pub_city columns of publisher table are being fetched and sorted against pub_name column. Since we have not specified any order keyword (ASC or DESC), by default, it is sorted in ascending order.
Code:
SELECT pub_name, country,pub_city
FROM publisher
ORDER BY pub_name;
Copy
Output:mysql> SELECT pub_name,country,pub_city -> FROM publisher -> ORDER BY pub_name; +——————————+———–+———–+ | pub_name | country | pub_city | +——————————+———–+———–+ | BPP Publication | India | Mumbai | | Jex Max Publication | USA | New York | | Mountain Publication | USA | Houstan | | New Harrold Publication | Australia | Adelaide | | Novel Publisher Ltd. | India | New Delhi | | Pieterson Grp. of Publishers | UK | Cambridge | | Summer Night Publication | USA | New York | | Ultra Press Inc. | UK | London | +——————————+———–+———–+ 8 rows in set (0.02 sec)
MySQL sorting rows in descending order
The following MySQL statement sort rows of a table in descending order using ORDER BY clause.
Code:
SELECT pub_name, country,pub_city
FROM publisher
ORDER BY pub_name DESC;
Copy
Output:mysql> SELECT pub_name,country,pub_city -> FROM publisher -> ORDER BY pub_name DESC; +——————————+———–+———–+ | pub_name | country | pub_city | +——————————+———–+———–+ | Ultra Press Inc. | UK | London | | Summer Night Publication | USA | New York | | Pieterson Grp. of Publishers | UK | Cambridge | | Novel Publisher Ltd. | India | New Delhi | | New Harrold Publication | Australia | Adelaide | | Mountain Publication | USA | Houstan | | Jex Max Publication | USA | New York | | BPP Publication | India | Mumbai | +——————————+———–+———–+ 8 rows in set (0.00 sec)
MySQL sorting rows on multiple columns
Sort can be performed on multiple columns. The way this type of sort happen is, firstly the rows will be sorted on the first column then the rows will be sorted on the second column whose first column’s data are same.
In the following MySQL statement, all records of pub_name, country and pub_city columns of publisher table are being fetched and sorted against country and pub_city columns. Since we have not specified any order keyword (ASC or DESC), by default, it is sorted in ascending order.
Code:
SELECT pub_name, country,pub_city
FROM publisher
ORDER BY country,pub_city;
Copy
Output :mysql> SELECT pub_name, country, pub_city -> FROM publisher -> ORDER BY country, pub_city; +——————————+———–+———–+ | pub_name | country | pub_city | +——————————+———–+———–+ | New Harrold Publication | Australia | Adelaide | | BPP Publication | India | Mumbai | | Novel Publisher Ltd. | India | New Delhi | | Pieterson Grp. of Publishers | UK | Cambridge | | Ultra Press Inc. | UK | London | | Mountain Publication | USA | Houstan | | Jex Max Publication | USA | New York | | Summer Night Publication | USA | New York | +——————————+———–+———–+ 8 rows in set (0.00 sec)
MySQL select with NULL value
IS NULL, IS NOT NULL is used to select or test if a value stored in a table is NULL. While writing a MySQL statement, NULL keyword is used to specify a null value.
What is NULL value ?
A value of NULL says that the value is unknown, not applicable or will be added later.
A value of NULL is not an empty or zero value.
No two null values are equal.
Since a value of NULL is unknown, comparisons between two null values, or between a NULL and any other value, returns unknown.
DELETE statement is used to remove rows from a table.
Version: MySQL 5.6
Single-table syntax:DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name [PARTITION (partition_name,…)] [WHERE where_condition] [ORDER BY …] [LIMIT row_count]
Explanation:
The DELETE statement deletes rows from table_name and returns the number of deleted rows. You can use ROW_COUNT() function to check the number of deleted rows.
The conditions in the WHERE clause (optional) identify which rows to delete.
Without WHERE clause, all rows are deleted.
If you specify the ORDER BY clause, the rows are deleted in specified order.
The LIMIT clause is used to place a limit on the number of rows that can be deleted. These clauses apply to single-table deletes, but not multi-table deletes.
Or :DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*]] … USING table_references [WHERE where_condition]
Important points:
Privileges: To delete a record(s) from a table, the user must have the DELETE privilege on that particular table.
The TRUNCATE TABLE statement is a faster way to empty a table than a DELETE statement with no WHERE clause. Here is a detail discussion on the difference between DELETE and TRUNCATE statement.
Subqueries: Currently there is no option to delete from a table and select from the same table in a subquery.
As of MySQL 5.6.2, DELETE supports explicit partition selection using the PARTITION option, which takes a comma-separated list of the names of one or more partitions or subpartitions (or both) from which to select rows to be dropped. Partitions not included in the list are ignored. Given a partitioned table t with a partition named p0, executing the statement DELETE FROM t PARTITION (p0) has the same effect on the table as executing ALTER TABLE t TRUNCATE PARTITION (p0); in both cases, all rows in partition p0 are dropped.
Auto-Increment Columns : If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value is not reused for a MyISAM or InnoDB table.
Example: MySQL DELETE specific rows or records
The following statement will remove those records from the ‘newcate’ table which satisfies the condition ‘cate_id’ = ‘CA002’.
Sample table: newcate
Code:
DELETE FROM newcate
WHERE cate_id='CA002';
Copy
Example: MySQL DELETE all rows or records
If not accompanied by any condition about which rows are to be removed, MySQL DELETE statement removes all records or rows from a table. The following statement will remove all the rows or records from ‘Newcate’ table.
Code:
DELETE FROM Newcate;
Copy
Example: MySQL DELETE with ORDER BY for limited number of rows
ORDER BY and LIMIT keyword can be used with MySQL DELETE statement to remove only a given number of rows, where columns are sorted in a specific order. The ORDER BY clause sorts the columns in a specific order and the LIMIT keyword deletes only the number rows mentioned by the numeric value immediately followed by LIMIT keyword. See the following example:
Sample table : newauthor
Code:
DELETE FROM newauthor
ORDER BY country DESC LIMIT 2;
Copy
The statement above will do the following – 1. order the rows of ‘newauthor’ table in descending order according to column ‘country’, 2. delete only two(2) rows for each ‘country’.
Example: MySQL DELETE rows using subqueries with alias and EXISTS
A subquery can be used with MySQL DELETE statement. This is useful when you want to delete rows depending upon a complex condition.
If we want to remove records from ‘newauthor’ table with following conditions – 1. ‘aut_id’ of ‘newauthor’ table must exist in ‘book_mast’ table, 2. ‘no_pages’ of ‘book_mast’ table must be more than 300, 3. ‘aut_id’ of ‘newauthor’ and ‘aut_id’ of ‘book_mast’ must match,
then execute the following code.
Sample table: newauthor
Sample table: book_mast
Code:
DELETE FROM newauthor
WHERE exists
(SELECT *
FROM book_mast
WHERE no_page>300
AND newauthor.aut_id=book_mast.aut_id);
Copy
MySQL TRUNCATE table
MySQL TRUNCATE TABLE statement is used to delete all the rows from a table. Apparently, this is similar to ‘DELETE FROM <TABLE NAME>’ statement,but they are quite different.
Difference between TRUNCATE and DELETE
TRUNCATE
DELETE
It is a DDL command (i.e. a command used to define the database structure or schema) and once you have deleted the rows using it, you can not use the ROLLBACK to undo the task.
It is a DML command (i.e. a command used for managing data) and can be rolled back.
You can’t use WHERE clause.
You can use WHERE clause.
Faster than DELETE.
Slower than TRUNCATE.
Syntax:TRUNCATE table<table_name>
Where table_name indicates name of the table
Example: MySQL TRUNCATE table
The following MySQL statement will delete all the rows from the newauthor table.
For a single table, the UPDATE statement updates columns of existing rows in the named table with new values. Specific columns can be modified using the SET clause by supplying new values for that column.
The WHERE clause can be used to specify the conditions those identify which rows to update. Without using WHERE clause, all rows are updated.
The ORDER BY clause is used to update the order that is already specified.
The LIMIT clause specifies a limit on the number of rows that can be updated.
For multiple tables, UPDATE updates row in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.
The UPDATE statement supports the following modifiers:
LOW_PRIORITY: Using LOW_PRIORITY keyword, execution of the UPDATE is delayed until no other clients are reading from the table. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).
IGNORE : Using IGNORE keyword, the update statement does not abort even if errors occur during the update. Rows for which duplicate-key conflicts occur are not updated. Rows for which columns are updated to values that would cause data conversion errors are updated to the closest valid values instead.
Following are some examples on MySQL update where we have used newpurchase as sample table.
Sample table: newpurchase
MySQL UPDATE column
MySQL UPDATE column can be used to update some specific columns. The following MySQL statement will update the ‘receive_qty’ column of newpurchase table with a new value 20.
UPDATE newpurchase SET receive_qty=20;
Copy
MySQL UPDATE with WHERE
MySQL UPDATE command can be used with WHERE clause to filter (against certain conditions) which rows will be updated. The following MySQL statement will update the ‘receive_qty’ column of newpurchase table with a new value 25 if the value of purch_price is more than 50.
UPDATE newpurchase
SET receive_qty=25
WHERE purch_price>50;
Copy
MySQL UPDATE using NULL
MySQL UPDATE command can be used to update a column value to NULL by setting column_name = NULL, where column_name is the name of the column to be updated. The following MySQL statement will update pub_lang column with NULL if purch_price is more than 50. In this statement, other columns are also updated with respective new values.
UPDATE newpurchase
SET receive_qty=20,pub_lang='Hindi',pub_lang=NULL
WHERE purch_price>50;
Copy
MySQL UPDATE multiple columns
MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated. The following MySQL statement will update receive_qty, pub_lang, and receive_dt columns with new values 20, Hindi and 2008-07-10 if purch_price is more than 50.
UPDATE newpurchase
SET receive_qty=20,pub_lang='Hindi',receive_dt='2008-07-10'
WHERE purch_price>50;
Copy
MySQL UPDATE with subqueries
Here in the following, we have discussed how to use MySQL UPDATE command with subqueries.
The following MySQL statement will update purch_price with purch_price multiplied by 5 if it satisfies the condition defined in the subquery started with SELECT wrapped within a pair of parenthesis.
The subquery retrieves only those cate_ids from purchase table if their corresponding receive_qty is more than 10.
UPDATE newpurchase
SET purch_price=purch_price*.05
WHERE cate_id IN(SELECT cate_id
FROM purchase
WHERE receive_qty>10);
Copy
Updating MySQL Table using PHP Script
You can update MySQL table data (using UPDATE command) through a PHP script. Within the script, PHP function MySQL_query() execute the SQL command. We have used a table called ‘item’ to apply the query: Table Name : item Structure : item_code varchar(20), value int(11), quantity int(11) where item_code is the primary key. In the following rows of item table, ‘value’ column which is marked with red rectangle will be updated.
If you want to update the val1 with 5,8 and 7 for concerned id 1,3 and 4 and the other val1 will remain same and the val2 will be updated with 13 and 5 for the concerned id 2 and 4 and the other will remain same, the following update statement can be used by using IF and CASE.
Code:
UPDATE table1 SET val1= CASE id
WHEN 1 THEN 5
WHEN 3 THEN 8
WHEN 4 THEN 7
ELSE val1
END,
val2= CASE id
WHEN 2 THEN 13
WHEN 4 THEN 5
ELSE val2
END
WHERE id IN (1, 2, 3, 4);
Copy
Pictorial presentation:
Sample Output:
Examples: MySQL UPDATE on multiple tables
Here we have used two tables book_mast and purchase for the following exampleas sample table. We have shown some of the columns in the associated tables. Here are the tables below -MySQL> SELECT book_id,book_name,pub_lang,book_price -> FROM book_mast; +——–+————————————+———+———-+ | book_id| book_name | pub_lang|book_price| +——–+————————————+———+———-+ | BK001 | Introduction to Electrodynamics | English | 85.00| | BK002 | Understanding of Steel Construction| English | 105.50| | BK003 | Guide to Networking | Hindi | 200.00| | BK004 | Transfer of Heat and Mass | English | 250.00| | BK005 | Conceptual Physics | NULL | 145.00| | BK006 | Fundamentals of Heat | German | 112.00| | BK007 | Advanced 3d Graphics | Hindi | 56.00| | BK008 | Human Anatomy | German | 50.50| | BK009 | Mental Health Nursing | English | 145.00| | BK010 | Fundamentals of Thermodynamics | English | 225.00| | BK011 | The Experimental Analysis of Cat | French | 95.00| | BK012 | The Nature of World | English | 88.00| | BK013 | Environment a Sustainable Future | German | 100.00| | BK014 | Concepts in Health | NULL | 180.00| | BK015 | Anatomy & Physiology | Hindi | 135.00| | BK016 | Networks and Telecommunications | French | 45.00| +——–+————————————+———+———-+ 16 rows in set (0.00 sec) and MySQL> SELECT book_id,pub_lang,purch_price,total_cost -> FROM purchase; +———+———-+————-+————+ | book_id | pub_lang | purch_price | total_cost | +———+———-+————-+————+ | BK001 | English | 75.00 | 1125.00 | | BK004 | English | 55.00 | 440.00 | | BK005 | NULL | 20.00 | 400.00 | | BK004 | English | 35.00 | 525.00 | | BK001 | English | 25.00 | 200.00 | | BK003 | Hindi | 45.00 | 900.00 | +———+———-+————-+————+ 6 rows in set (0.02 sec)
If we want to update the book_price of the table book_mast by an increment of 5% and also update the purch_price and total_cost of puchase table by an increment of 5%, and this increment will affect only those rows in both book_mast and purchase table, which publishing language is English and book_id matching in both the tables, we can write the following code –
UPDATE book_mast,purchase
SET book_mast.book_price=book_mast.book_price+(book_mast.book_price*.05),
purchase.purch_price=purchase.purch_price+(purchase.purch_price*.05),
purchase.total_cost=receive_qty*(purchase.purch_price+(purchase.purch_price*.05))
WHERE book_mast.book_id=purchase.book_id
AND purchase.pub_lang="English";
Copy
After updating it is to be shown that, the highlighted rows have been effected in both the tables.MySQL> SELECT book_id,book_name,pub_lang,book_price -> FROM book_mast; +——–+————————————+———+———-+ | book_id| book_name | pub_lang|book_price| +——–+————————————+———+———-+ | BK001 | Introduction to Electrodynamics | English | 89.25| | BK002 | Understanding of Steel Construction| English | 105.50| | BK003 | Guide to Networking | Hindi | 200.00| | BK004 | Transfer of Heat and Mass | English | 262.50| | BK005 | Conceptual Physics | NULL | 145.00| | BK006 | Fundamentals of Heat | German | 112.00| | BK007 | Advanced 3d Graphics | Hindi | 56.00| | BK008 | Human Anatomy | German | 50.50| | BK009 | Mental Health Nursing | English | 145.00| | BK010 | Fundamentals of Thermodynamics | English | 225.00| | BK011 | The Experimental Analysis of Cat | French | 95.00| | BK012 | The Nature of World | English | 88.00| | BK013 | Environment a Sustainable Future | German | 100.00| | BK014 | Concepts in Health | NULL | 180.00| | BK015 | Anatomy & Physiology | Hindi | 135.00| | BK016 | Networks and Telecommunications | French | 45.00| +——–+————————————+———+———-+ 16 rows in set (0.01 sec) and MySQL> SELECT book_id,pub_lang,purch_price,total_cost -> FROM purchase; +———+———-+————-+————+ | book_id | pub_lang | purch_price | total_cost | +———+———-+————-+————+ | BK001 | English | 78.75 | 1181.25 | | BK004 | English | 57.75 | 462.00 | | BK005 | NULL | 20.00 | 400.00 | | BK004 | English | 36.75 | 551.25 | | BK001 | English | 26.25 | 210.00 | | BK003 | Hindi | 45.00 | 900.00 | +———+———-+————-+————+ 6 rows in set (0.08 sec)
MySQL: Update with Join Statement
Sample tables
Problem
If we want to update the aval1of table11 with the bval1 of table12 against the following condition –
1). the id of table11 and table13 must be matched, and
2). bval2 of table12 must be matched with the cval1 of table13 –
then the following code can be used.
Code:
UPDATE table11, table12, table13
SET table11.aval1 = table12.bval1
WHERE table11.id = table13.id
AND table12.bval2 = table13.cval1
MySQL INSERT statement is used to insert record(s) or row(s) into a table. The insertion of records or rows in the table can be done in two ways, insert a single row at a time, and insert multiple rows at a time.
Using the LOW_PRIORITY keyword, execution of the INSERT is delayed until no other clients are reading from the table. This includes other clients that began reading while existing clients are reading. Therefore it is possible, for a client that issues an INSERT LOW_PRIORITY statement to wait for a very long time (or even forever) in a read-heavy environment.
DELAYED
Using DELAYED keyword, the server puts the row or rows to be inserted into a buffer, and the INSERT DELAYED statement (issued by the client) can then continue immediately. The server holds the rows if the table is in use. The server begins inserting rows, when the table is free, checking periodically to see whether there are any new read requests for the table. If there are any new read requests, the delayed row queue is suspended until the table becomes free again.
HIGH_PRIORITY
Using HIGH_PRIORITY, it overrides the effect of the –low-priority-updates option if the server was started with that option. It also causes concurrent inserts not to be used. LOW_PRIORITY and HIGH_PRIORITY affect only storage engines that use only table-level locking such as MyISAM, MEMORY, and MERGE.
IGNORE
Using the IGNORE keyword, errors that occur while executing the INSERT statement are ignored.
INTO
Inserts new rows into an existing table.
tbl_name
Name of the table where rows will be inserted.
PARTITION
In MySQL 5.6.2 and later, you can control which partitions and subpartitions accept new rows when inserting into a partitioned table. The PARTITION option takes a comma-separated list of the names of one or more partitions or subpartitions (or both) of the table. If any of the rows which are ready to insert, by a given INSERT statement do not match one of the partitions listed, the INSERT statement fails with the error Found a row not matching the given partition set.
partition_name
Name of the partitioned table(s).
col_name
A comma-separated list of column names.You can specify a comma-separated list of column names following the table name and a value for each named column must be provided by the VALUES list or the SELECT statement.If you do not specify the column names for INSERT … VALUES or INSERT … SELECT, values for every column in the table must be provided by the VALUES list (same as order of the columns in the table) or the SELECT statement.The SET clause indicates the column names explicitly.
VALUES | VALUE
If strict SQL mode is off, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values.The keyword DEFAULT is used to set a column explicitly to its default value.If both the column list and the VALUES list are empty, INSERT creates a row with each column set to its default value.You can specify an expression expr to provide a column value. This might involve type conversion if the type of the expression does not match the type of the column, and conversion of a given value can result in different inserted values depending on the data type. For example, inserting the string ‘1998.0e-2’ into an INT, FLOAT, DECIMAL(10,6), or YEAR column results in the values 1998, 19.9821, 19.982100, and 1998 being inserted, respectively.
INSERT … SELECT
To insert many rows quickly into a table from one or many tables you can use INSERT … SELECT statement. Here is the syntax :
The following conditions hold for a INSERT … SELECT statements :
Using the IGNORE keyword, ignore rows that would cause duplicate-key violations.
The keyword DELAYED is ignored with INSERT … SELECT.
The target table of the INSERT statement may appear in the FROM clause of the SELECT part of the query.
AUTO_INCREMENT columns work as usual.
To avoid ambiguous column reference problems when the SELECT and the INSERT refer to the same table.
In MySQL 5.6.2 and later, you can control which partitions and subpartitions accept new rows when inserting into a partitioned table.
Example:INSERT INTO tb2 (fld_id) SELECT tbl.fld_order_id FROM tbl WHERE tbl.fld_order_id > 200;
INSERT DELAYED
The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that can be used for certain kinds of tables (such as MyISAM). When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.
Syntax:INSERT DELAYED …
INSERT … ON DUPLICATE KEY UPDATE
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs a UPDATE of the old row. For example, if column x is declared as UNIQUE and contains the value 1, the following two statements have similar effect:INSERT INTO table (x,y,z) VALUES (1,2,3) ON DUPLICATE KEY UPDATE z=z+1; UPDATE table SET z=z+1 WHERE x=1;
Note: The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.
Examples:
MySQL INSERT INTO statement is used to insert record(s) or row(s) into a table.
Syntax:INSERT INTO table_name ([column_name],[…column_name],…) VALUES( [column_value],[..column_value]);
Example with PHP code to insert data into a MySQL table
<?php
$host="localhost"; //yout host name
$username="root"; //yout user name
$password=""; // your password
$db_name="test"; // your database name
$con=MySQL_connect("$host", "$username", "$password")or die("cannot connect"); //MySQL connection
MySQL_select_db("$db_name")or die("can not select DB"); //select your database
$name = $_POST['name'];
$email = $_POST['email'];
$address = $_POST['address'];
$query = "INSERT INTO test (name,email,address) VALUES ('$name', '$email', '$address')";
MySQL_query($query) or die('Query "' . $query . '" failed: ' . MySQL_error());
// name, email and address are fields of your fields; test your table. $name, $email and $address are values collected from the form
?>
Copy
MySQL: INSERT one row in a table
The following statement inserts a single row into a table using MySQL INSERT INTO statement.
Code:
INSERT INTO newcate
VALUES ("CA006","Sports");
Copy
The above statement will insert one row in the table ‘newcate’. We have not mentioned any column name here. That is why all of the columns will be affected.mysql> select * from newcate; +———+————–+ | cate_id | cate_descrip | +———+————–+ | CA001 | Science | | CA002 | Technology | | CA003 | Computers | | CA004 | Nature | | CA005 | Medical | | CA006 | Sports | +———+————–+ 6 rows in set (0.00 sec)
MySQL: INSERT values for specific columns
The following statement inserts values for specific columns using MySQL INSERT INTO statement.
Sample table: newpurchase
Code:
INSERT INTO newpurchase (invoice_no,ord_no,book_name)
VALUES ("INV001","ORD006",”An advance book of Computer”);
Copy
The above statement will insert one(1) row in the table ‘newpurchase’ for the columns ‘invoice_no’, ‘ord_no’, and ‘book_name’.
MySQL: INSERT NULL values
The following statement inserts NULL values into one or more columns using MySQL INSERT INTO statement.
Sample table: newpurchase
Code:
INSERT INTO newpurchase (invoice_no,ord_no,book_name)
VALUES ("INV002","ORD007",NULL);
Copy
The above statement will insert one(1) row in the table ‘newpurchase’. Columns ‘invoice_no’, ‘ord_no’, and ‘book_name’ got populated with values where as column ‘book_name’ got populated with the NULL value.
Inserting multiple rows in a single SQL query
In MySQL, you can insert multiple rows in a single SQL query. Here is the syntax:INSERT INTO Table ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 );
MySQL INSERT rows with SELECT statement
The following statement inserts values into a table using MySQL INSERT INTO statement when the column names and values are collected from another identical table using MySQL SELECT statement. This way you can insert values of one table into another when tables are identical.
Sample table : purchase
Code:
INSERT INTO testpurchase
SELECT *
FROM purchase;
Copy
MySQL INSERT rows with SELECT statement and WHERE
The following statement inserts values into a table using MySQL INSERT INTO statement when the column names and values are collected from another identical table using MySQL SELECT and WHERE. This way you can insert values based upon some conditions of one table into another when tables are identical.
Sample table: purchase
Code:
INSERT INTO testpurchase
SELECT *
FROM purchase
WHERE YEAR(invoice_dt)='2008';
Copy
The above statement performs the following operations –
insert rows into ‘testpurchase’ table from the identical table ‘purchase’,
the year of ‘invoice_date’ of ‘purchase’ table must be ‘2008’ .
Partitioning (a database design technique) improves performance, manageability, simplifies maintenance and reduce the cost of storing large amounts of data. Partitioning can be achieved without splitting tables by physically putting tables on individual disk drives. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, therefore queries that access only a fraction of the data can run faster because there is fewer data to scan. There are two major forms of partitioning :
Horizontal Partitioning : Horizontal partitioning divides table rows into multiple partitions (based on a logic). All columns defined to a table are found in each partition, so no actual table attributes are missing. All the partition can be addressed individually or collectively. For example, a table that contains whole year sale transaction being partitioned horizontally into twelve distinct partitions, where each partition contains one month’s data.
Vertical Partitioning : Vertical partitioning divides a table into multiple tables that contain fewer columns. Like horizontal partitioning, in vertical partitioning a query scan fewer data which increases query performance. For example, a table that contains a number of very wide text or BLOB columns that aren’t addressed often being broken into two tables that have the most referenced columns in one table and the text or BLOB data in another.
MySQL partitioning
Version: MySQL 5.6
MySQL supports basic table partitioning but does not support vertical partitioning ( MySQL 5.6). This section describes in detail how to implement partitioning as part of your database.
By checking the output of the SHOW PLUGINS statement you will be sure whether your MySQL server supports the partition or not. See the following output :
Sample Output:mysql> SHOW PLUGINS; +—————————-+———-+——————–+———+———+ | Name | Status | Type | Library | License | +—————————-+———-+——————–+———+———+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | MySQL_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | MySQL_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | —————————| —— | —————— | —- | — | | —————————| —— | —————— | —- | — | | —————————| —— | —————— | —- | — | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +—————————-+———-+——————–+———+———+ 42 rows in set (0.21 sec)
MySQL 5.6 Community binaries include partitioning support.
Enable and disable partitioning support :
To enable partitioning (if you are compiling MySQL 5.6 from source), the build must be configured with the -DWITH_PARTITION_STORAGE_ENGINE option.
To disable partitioning support, you can start the MySQL Server with the –skip-partition option, in which case the value of have_partitioning is DISABLED.
How to partition a table?
In MySQL you can partition a table using CREATE TABLE or ALTER TABLE command. See the following CREATE TABLE syntax :CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,…)
ALTER TABLE statement can be used for adding, dropping, merging, and splitting partitions, and for performing partitioning maintenance. Here we have defined a nonpartitioned table:
This table can be partitioned by HASH (or in another type), using the bill_no column as the partitioning key, into 6 (or other) partitions using ALTER TABLE statement :
ALTER TABLE t1
PARTITION BY HASH(id)
PARTITIONS 6;
Copy
Partition naming :
Names of partitions follow the rules of other MySQL identifiers such as databases, tables, constraint, stored procedure etc. Partition names are not case-sensitive.
Advantages of partitioning
During the scan operation, MySQL optimizer accesses those partitions that will satisfy a particular query. For example, a whole year sale records table may be broken up into 4 partitions (i.e. sale data from of Apr-Jun (partition p0), Jul-Sep (partition p1) , Oct-Dec (partition p2), Jam-Mar (partition p0)) . If a query is issued that contains sale data between Jul-Sep quarter, then it scans the partition p1 only instead of total table records and the query will complete much sooner.
Partitioning allows you to have more control over how data is managed inside the database. For example, you can drop specific partitions in a partitioned table where data loses its usefulness. The process of adding new data, in some cases, be greatly facilitated by adding one or more new partitions for storing that data using ALTER TABLE command.
In partitioning, it is possible to store more data in one table than can be held on a single disk or file system partition.
MySQL 5.6 supports explicit partition selection for queries. For example, SELECT * FROM table1 PARTITION (p0,p1) WHERE col1< 10 selects only those rows in partitions p0 and p1 that match the WHERE condition, this can greatly speed up queries
Partition selection also supports the data modification statements DELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML.
Types of MySQL partitioning
Following types of partitioning are available in MySQL 5.6 :
In MySQL, RANGE partitioning mode allows us to specify various ranges for which data is assigned. Ranges should be contiguous but not overlapping, and are defined using the VALUES LESS THAN operator. In the following example, sale_mast table contains four columns bill_no, bill_date, cust_code and amount. This table can be partitioned by range in various of ways, depending on your requirement. Here we have used the bill_date column and decide to partition the table 4 ways by adding a PARTITION BY RANGE clause. In these partitions the range of the sale date (sale_date) are as of follow :
partition p0 ( sale between 01-01-2013 to 31-03-2013)
partition p1 ( sale between 01-04-2013 to 30-06-2013)
partition p2 ( sale between 01-07-2013 to 30-09-2013)
partition p3 ( sale between 01-10-2013 to 30-12-2013)
Let create the table :mysql> CREATE TABLE sale_mast (bill_no INT NOT NULL, bill_date TIMESTAMP NOT NULL, cust_code VARCHAR(15) NOT NULL, amount DECIMAL(8,2) NOT NULL) PARTITION BY RANGE (UNIX_TIMESTAMP(bill_date))( PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP(‘2013-04-01’)), PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP(‘2013-07-01’)), PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP(‘2013-10-01’)), PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP(‘2014-01-01’))); Query OK, 0 rows affected (1.50 sec)
Here is the partition status of sale_mast table:mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=’sale_mast’; +—————-+————+ | PARTITION_NAME | TABLE_ROWS | +—————-+————+ | p0 | 4 | | p1 | 3 | | p2 | 2 | | p3 | 0 | +—————-+————+ 4 rows in set (0.02 sec)
In the above way you can partition the table based on sale amount (amount. In these partitions the range of the sale amount (amount) are as of follow :
partition p0 ( sale amount < 100 )
partition p1 ( sale amount < 500 )
partition p2 ( sale amount <1000 )
partition p3 ( sale amount<1500 )
Let create the table :mysql> CREATE TABLE sale_mast1 (bill_no INT NOT NULL, bill_date TIMESTAMP NOT NULL, cust_codE VARCHAR(15) NOT NULL, amount INT NOT NULL) PARTITION BY RANGE (amount) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (500), PARTITION p2 VALUES LESS THAN (1000), PARTITION p3 VALUES LESS THAN (1500)); Query OK, 0 rows affected (1.34 sec)
Drop a MySQL partition
If you feel some data are useless in a partitioned table you can drop one or more partition(s). To delete all rows from partition p0 of sale_mast, you can use the following statement :MySQL> ALTER TABLE sale_mast TRUNCATE PARTITION p0; Query OK, 0 rows affected (0.49 sec) mysql> SELECT * FROM sale_mast; +———+———————+———–+——–+ | bill_no | bill_date | cust_code | amount | +———+———————+———–+——–+ | 5 | 2013-04-19 00:00:00 | C234 | 656.00 | | 6 | 2013-05-31 00:00:00 | C743 | 854.00 | | 7 | 2013-06-11 00:00:00 | C234 | 542.00 | | 8 | 2013-07-24 00:00:00 | C003 | 300.00 | | 9 | 2013-08-02 00:00:00 | C456 | 475.20 | +———+———————+———–+——–+ 5 rows in set (0.01 sec)
Here is the partition status of sale_mast after dropping the partition p0 :MySQL> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=’sale_mast’; +—————-+————+ | PARTITION_NAME | TABLE_ROWS | +—————-+————+ | p0 | 0 | | p1 | 3 | | p2 | 2 | | p3 | 0 | +—————-+————+ 4 rows in set (0.05 sec)
MySQL LIST Partitioning
List partition allows us to segment data based on a pre-defined set of values (e.g. 1, 2, 3). This is done by using PARTITION BY LIST(expr) where expr is a column value and then defining each partition by means of a VALUES IN (value_list), where value_list is a comma-separated list of integers. In MySQL 5.6, it is possible to match against only a list of integers (and possibly NULL) when partitioning by LIST. In the following example, sale_mast2 table contains four columns bill_no, bill_date, agent_code, and amount. Suppose there are 11 agents represent three cities A, B, C these can be arranged in three partitions with LIST Partitioning as follows :
City
Agent ID
A
1, 2, 3
B
4, 5, 6
C
7, 8, 9, 10, 11
Let create the table :mysql> CREATE TABLE sale_mast2 (bill_no INT NOT NULL, bill_date TIMESTAMP NOT NULL, agent_codE INT NOT NULL, amount INT NOT NULL) PARTITION BY LIST(agent_code) ( PARTITION pA VALUES IN (1,2,3), PARTITION pB VALUES IN (4,5,6), PARTITION pC VALUES IN (7,8,9,10,11)); Query OK, 0 rows affected (1.17 sec)
MySQL COLUMNS Partitioning
In COLUMNS partitioning it is possible to use multiple columns in partitioning keys. There are two types of COLUMNS partitioning :
In addition, both RANGE COLUMNS partitioning and LIST COLUMNS partitioning support the use of non-integer columns for defining value ranges or list members. The permitted data types are shown in the following list:
Both RANGE COLUMNS partitioning and LIST COLUMNS partitioning support following data types for defining value ranges or list members.
All integer types: TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT.
DATE and DATETIME.
RANGE COLUMNS partitioning
RANGE COLUMNS partitioning is similar to range partitioning with some significant difference. RANGE COLUMNS accepts a list of one or more columns as partition keys. You can define the ranges using various columns of types (mentioned above) other than integer types.
Here is the basic syntax for creating a table partitioned by RANGE COLUMNS :CREATE TABLE table_name PARTITIONED BY RANGE COLUMNS(column_list) ( PARTITION partition_name VALUES LESS THAN (value_list)[, PARTITION partition_name VALUES LESS THAN (value_list)][, …] ) column_list: column_name[, column_name][, …] value_list: value[, value][, …]
column_list is a list of one or more columns.
value_list is a list of values and must be supplied for each partition definition.
column list and in the value list defining each partition must occur in the same order
The order of the column names in the partitioning column list and the value lists do not have to be the same as the order of the table column definitions in CREATE TABLE statement.
Here is an example :mysql> CREATE TABLE table3 (col1 INT, col2 INT, col3 CHAR(5), col4 INT) PARTITION BY RANGE COLUMNS(col1, col2, col3) (PARTITION p0 VALUES LESS THAN (50, 100, ‘aaaaa’), PARTITION p1 VALUES LESS THAN (100,200,’bbbbb’), PARTITION p2 VALUES LESS THAN (150,300,’ccccc’), PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE)); Query OK, 0 rows affected (1.39 sec)
In the above example –
Table table3 contains the columns col1, col2, col3, col4
The first three columns have participated in partitioning COLUMNS clause, in the order col1, col2, col3.
Each value list used to define a partition contains 3 values in the same order and (INT, INT, CHAR(5)) form.
LIST COLUMNS partitioning
LIST COLUMNS accepts a list of one or more columns as partition keys.You can use various columns of data of types other than integer types as partitioning columns. You can use string types, DATE, and DATETIME columns
In a company there are agents in 3 cities, for sales and marketing purposes. We have organized the agents in 3 cities as shown in the following table :
City
Agent ID
A
A1, A2, A3
B
B1, B2, B3
C
C1, C2, C3, C4, C5
Let create a table with LIST COLUMNS partitioning based on the above information :mysql> CREATE TABLE salemast ( agent_id VARCHAR(15), agent_name VARCHAR(50), agent_address VARCHAR(100), city_code VARCHAR(10)) PARTITION BY LIST COLUMNS(agent_id) ( PARTITION pcity_a VALUES IN(‘A1’, ‘A2’, ‘A3’), PARTITION pcity_b VALUES IN(‘B1’, ‘B2’, ‘B3’), PARTITION pcity_c VALUES IN (‘C1’, ‘C2’, ‘C3’, ‘C4’, ‘C5’)); Query OK, 0 rows affected (1.06 sec)
You can use DATE and DATETIME columns in LIST COLUMNS partitioning, see the following example :CREATE TABLE sale_master (bill_no INT NOT NULL, bill_date DATE, cust_code VARCHAR(15) NOT NULL, amount DECIMAL(8,2) NOT NULL) PARTITION BY RANGE COLUMNS (bill_date)( PARTITION p_qtr1 VALUES LESS THAN (‘2013-04-01’), PARTITION p_qtr2 VALUES LESS THAN (‘2013-07-01’), PARTITION p_qtr3 VALUES LESS THAN (‘2013-10-01’), PARTITION p_qtr4 VALUES LESS THAN (‘2014-01-01’));
MySQL HASH Partitioning
MySQL HASH partition is used to distribute data among a predefined number of partitions on a column value or expression based on a column value. This is done by using PARTITION BY HASH(expr) clause, adding in CREATE TABLE STATEMENT. In PARTITIONS num clause, num is a positive integer represents the number of partitions of the table. The following statement creates a table that uses hashing on the studetn_id column and is divided into 4 partitions :
MySQL>CREATE TABLE student (student_id INT NOT NULL,
class VARCHAR(8), name VARCHAR(40),
date_of_admission DATE NOT NULL DEFAULT '2000-01-01')
PARTITION BY HASH(student_id)
PARTITIONS 4;
Query OK, 0 rows affected (1.43 sec)
Copy
It is also possible to make a partition based on the year in which a student was admitted. See the following statement :
MySQL> CREATE TABLE student (student_id INT NOT NULL,
class VARCHAR(8), class VARCHAR(8), name VARCHAR(40),
date_of_admission DATE NOT NULL DEFAULT '2000-01-01')
PARTITION BY HASH(YEAR(date_of_admission))
PARTITIONS 4;
Query OK, 0 rows affected (1.27 sec)
Copy
MySQL KEY Partitioning
MySQL KEY partition is a special form of HASH partition, where the hashing function for key partitioning is supplied by the MySQL server. The server employs its own internal hashing function which is based on the same algorithm as PASSWORD(). This is done by using PARTITION BY KEY, adding in CREATE TABLE STATEMENT. In KEY partitioning KEY takes only a list of zero or more column names. Any columns used as the partitioning key must comprise part or all of the table’s primary key if the table has one. If there is a primary key in a table, it is used as partitioning key when no column is specified as the partitioning key. Here is an example :MySQL> CREATE TABLE table1 ( id INT NOT NULL PRIMARY KEY, fname VARCHAR(25), lname VARCHAR(25)) PARTITION BY KEY() PARTITIONS 2; Query OK, 0 rows affected (0.84 sec)
If there is no primary key but there is a unique key in a table, then the unique key is used for the partitioning key :MySQL> CREATE TABLE table2 ( id INT NOT NULL, fname VARCHAR(25), lname VARCHAR(25), UNIQUE KEY (id)) PARTITION BY KEY() PARTITIONS 2; Query OK, 0 rows affected (0.77 sec)
MySQL Subpartitioning
Subpartitioning is a method to divide each partition further in a partitioned table. See the following CREATE TABLE statement :
CREATE TABLE table10 (BILL_NO INT, sale_date DATE, cust_code VARCHAR(15),
AMOUNT DECIMAL(8,2))
PARTITION BY RANGE(YEAR(sale_date) )
SUBPARTITION BY HASH(TO_DAYS(sale_date))
SUBPARTITIONS 4 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (2010),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
Copy
In the above statement –
The table has 4 RANGE partitions.
Each of these partitions—p0, p1, p2 and p3—is further divided into 4 subpartitions.
Therefore the entire table is divided into 4 * 4 = 16 partitions.
MySQL INSERT statement is used to insert record(s) or row(s) into a table. The insertion of records or rows in the table can be done in two ways, insert a single row at a time, and insert multiple rows at a time.
Using the LOW_PRIORITY keyword, execution of the INSERT is delayed until no other clients are reading from the table. This includes other clients that began reading while existing clients are reading. Therefore it is possible, for a client that issues an INSERT LOW_PRIORITY statement to wait for a very long time (or even forever) in a read-heavy environment.
DELAYED
Using DELAYED keyword, the server puts the row or rows to be inserted into a buffer, and the INSERT DELAYED statement (issued by the client) can then continue immediately. The server holds the rows if the table is in use. The server begins inserting rows, when the table is free, checking periodically to see whether there are any new read requests for the table. If there are any new read requests, the delayed row queue is suspended until the table becomes free again.
HIGH_PRIORITY
Using HIGH_PRIORITY, it overrides the effect of the –low-priority-updates option if the server was started with that option. It also causes concurrent inserts not to be used. LOW_PRIORITY and HIGH_PRIORITY affect only storage engines that use only table-level locking such as MyISAM, MEMORY, and MERGE.
IGNORE
Using the IGNORE keyword, errors that occur while executing the INSERT statement are ignored.
INTO
Inserts new rows into an existing table.
tbl_name
Name of the table where rows will be inserted.
PARTITION
In MySQL 5.6.2 and later, you can control which partitions and subpartitions accept new rows when inserting into a partitioned table. The PARTITION option takes a comma-separated list of the names of one or more partitions or subpartitions (or both) of the table. If any of the rows which are ready to insert, by a given INSERT statement do not match one of the partitions listed, the INSERT statement fails with the error Found a row not matching the given partition set.
partition_name
Name of the partitioned table(s).
col_name
A comma-separated list of column names.You can specify a comma-separated list of column names following the table name and a value for each named column must be provided by the VALUES list or the SELECT statement.If you do not specify the column names for INSERT … VALUES or INSERT … SELECT, values for every column in the table must be provided by the VALUES list (same as order of the columns in the table) or the SELECT statement.The SET clause indicates the column names explicitly.
VALUES | VALUE
If strict SQL mode is off, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values.The keyword DEFAULT is used to set a column explicitly to its default value.If both the column list and the VALUES list are empty, INSERT creates a row with each column set to its default value.You can specify an expression expr to provide a column value. This might involve type conversion if the type of the expression does not match the type of the column, and conversion of a given value can result in different inserted values depending on the data type. For example, inserting the string ‘1998.0e-2’ into an INT, FLOAT, DECIMAL(10,6), or YEAR column results in the values 1998, 19.9821, 19.982100, and 1998 being inserted, respectively.
INSERT … SELECT
To insert many rows quickly into a table from one or many tables you can use INSERT … SELECT statement. Here is the syntax :
The following conditions hold for a INSERT … SELECT statements :
Using the IGNORE keyword, ignore rows that would cause duplicate-key violations.
The keyword DELAYED is ignored with INSERT … SELECT.
The target table of the INSERT statement may appear in the FROM clause of the SELECT part of the query.
AUTO_INCREMENT columns work as usual.
To avoid ambiguous column reference problems when the SELECT and the INSERT refer to the same table.
In MySQL 5.6.2 and later, you can control which partitions and subpartitions accept new rows when inserting into a partitioned table.
Example:INSERT INTO tb2 (fld_id) SELECT tbl.fld_order_id FROM tbl WHERE tbl.fld_order_id > 200;
INSERT DELAYED
The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that can be used for certain kinds of tables (such as MyISAM). When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.
Syntax:INSERT DELAYED …
INSERT … ON DUPLICATE KEY UPDATE
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs a UPDATE of the old row. For example, if column x is declared as UNIQUE and contains the value 1, the following two statements have similar effect:INSERT INTO table (x,y,z) VALUES (1,2,3) ON DUPLICATE KEY UPDATE z=z+1; UPDATE table SET z=z+1 WHERE x=1;
Note: The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.
Examples:
MySQL INSERT INTO statement is used to insert record(s) or row(s) into a table.
Syntax:INSERT INTO table_name ([column_name],[…column_name],…) VALUES( [column_value],[..column_value]);
Example with PHP code to insert data into a MySQL table
<?php
$host="localhost"; //yout host name
$username="root"; //yout user name
$password=""; // your password
$db_name="test"; // your database name
$con=MySQL_connect("$host", "$username", "$password")or die("cannot connect"); //MySQL connection
MySQL_select_db("$db_name")or die("can not select DB"); //select your database
$name = $_POST['name'];
$email = $_POST['email'];
$address = $_POST['address'];
$query = "INSERT INTO test (name,email,address) VALUES ('$name', '$email', '$address')";
MySQL_query($query) or die('Query "' . $query . '" failed: ' . MySQL_error());
// name, email and address are fields of your fields; test your table. $name, $email and $address are values collected from the form
?>
Copy
MySQL: INSERT one row in a table
The following statement inserts a single row into a table using MySQL INSERT INTO statement.
Code:
INSERT INTO newcate
VALUES ("CA006","Sports");
Copy
The above statement will insert one row in the table ‘newcate’. We have not mentioned any column name here. That is why all of the columns will be affected.mysql> select * from newcate; +———+————–+ | cate_id | cate_descrip | +———+————–+ | CA001 | Science | | CA002 | Technology | | CA003 | Computers | | CA004 | Nature | | CA005 | Medical | | CA006 | Sports | +———+————–+ 6 rows in set (0.00 sec)
MySQL: INSERT values for specific columns
The following statement inserts values for specific columns using MySQL INSERT INTO statement.
Sample table: newpurchase
Code:
INSERT INTO newpurchase (invoice_no,ord_no,book_name)
VALUES ("INV001","ORD006",”An advance book of Computer”);
Copy
The above statement will insert one(1) row in the table ‘newpurchase’ for the columns ‘invoice_no’, ‘ord_no’, and ‘book_name’.
MySQL: INSERT NULL values
The following statement inserts NULL values into one or more columns using MySQL INSERT INTO statement.
Sample table: newpurchase
Code:
INSERT INTO newpurchase (invoice_no,ord_no,book_name)
VALUES ("INV002","ORD007",NULL);
Copy
The above statement will insert one(1) row in the table ‘newpurchase’. Columns ‘invoice_no’, ‘ord_no’, and ‘book_name’ got populated with values where as column ‘book_name’ got populated with the NULL value.
Inserting multiple rows in a single SQL query
In MySQL, you can insert multiple rows in a single SQL query. Here is the syntax:INSERT INTO Table ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 );
MySQL INSERT rows with SELECT statement
The following statement inserts values into a table using MySQL INSERT INTO statement when the column names and values are collected from another identical table using MySQL SELECT statement. This way you can insert values of one table into another when tables are identical.
Sample table : purchase
Code:
INSERT INTO testpurchase
SELECT *
FROM purchase;
Copy
MySQL INSERT rows with SELECT statement and WHERE
The following statement inserts values into a table using MySQL INSERT INTO statement when the column names and values are collected from another identical table using MySQL SELECT and WHERE. This way you can insert values based upon some conditions of one table into another when tables are identical.
Sample table: purchase
Code:
INSERT INTO testpurchase
SELECT *
FROM purchase
WHERE YEAR(invoice_dt)='2008';
Copy
The above statement performs the following operations –
Partitioning (a database design technique) improves performance, manageability, simplifies maintenance and reduce the cost of storing large amounts of data. Partitioning can be achieved without splitting tables by physically putting tables on individual disk drives. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, therefore queries that access only a fraction of the data can run faster because there is fewer data to scan. There are two major forms of partitioning :
Horizontal Partitioning : Horizontal partitioning divides table rows into multiple partitions (based on a logic). All columns defined to a table are found in each partition, so no actual table attributes are missing. All the partition can be addressed individually or collectively. For example, a table that contains whole year sale transaction being partitioned horizontally into twelve distinct partitions, where each partition contains one month’s data.
Vertical Partitioning : Vertical partitioning divides a table into multiple tables that contain fewer columns. Like horizontal partitioning, in vertical partitioning a query scan fewer data which increases query performance. For example, a table that contains a number of very wide text or BLOB columns that aren’t addressed often being broken into two tables that have the most referenced columns in one table and the text or BLOB data in another.
MySQL partitioning
Version: MySQL 5.6
MySQL supports basic table partitioning but does not support vertical partitioning ( MySQL 5.6). This section describes in detail how to implement partitioning as part of your database.
By checking the output of the SHOW PLUGINS statement you will be sure whether your MySQL server supports the partition or not. See the following output :
Sample Output:mysql> SHOW PLUGINS; +—————————-+———-+——————–+———+———+ | Name | Status | Type | Library | License | +—————————-+———-+——————–+———+———+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | MySQL_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | MySQL_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | —————————| —— | —————— | —- | — | | —————————| —— | —————— | —- | — | | —————————| —— | —————— | —- | — | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +—————————-+———-+——————–+———+———+ 42 rows in set (0.21 sec)
MySQL 5.6 Community binaries include partitioning support.
Enable and disable partitioning support :
To enable partitioning (if you are compiling MySQL 5.6 from source), the build must be configured with the -DWITH_PARTITION_STORAGE_ENGINE option.
To disable partitioning support, you can start the MySQL Server with the –skip-partition option, in which case the value of have_partitioning is DISABLED.
How to partition a table?
In MySQL you can partition a table using CREATE TABLE or ALTER TABLE command. See the following CREATE TABLE syntax :CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,…)
ALTER TABLE statement can be used for adding, dropping, merging, and splitting partitions, and for performing partitioning maintenance. Here we have defined a nonpartitioned table:
This table can be partitioned by HASH (or in another type), using the bill_no column as the partitioning key, into 6 (or other) partitions using ALTER TABLE statement :
ALTER TABLE t1
PARTITION BY HASH(id)
PARTITIONS 6;
Copy
Partition naming :
Names of partitions follow the rules of other MySQL identifiers such as databases, tables, constraint, stored procedure etc. Partition names are not case-sensitive.
Advantages of partitioning
During the scan operation, MySQL optimizer accesses those partitions that will satisfy a particular query. For example, a whole year sale records table may be broken up into 4 partitions (i.e. sale data from of Apr-Jun (partition p0), Jul-Sep (partition p1) , Oct-Dec (partition p2), Jam-Mar (partition p0)) . If a query is issued that contains sale data between Jul-Sep quarter, then it scans the partition p1 only instead of total table records and the query will complete much sooner.
Partitioning allows you to have more control over how data is managed inside the database. For example, you can drop specific partitions in a partitioned table where data loses its usefulness. The process of adding new data, in some cases, be greatly facilitated by adding one or more new partitions for storing that data using ALTER TABLE command.
In partitioning, it is possible to store more data in one table than can be held on a single disk or file system partition.
MySQL 5.6 supports explicit partition selection for queries. For example, SELECT * FROM table1 PARTITION (p0,p1) WHERE col1< 10 selects only those rows in partitions p0 and p1 that match the WHERE condition, this can greatly speed up queries
Partition selection also supports the data modification statements DELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML.
Types of MySQL partitioning
Following types of partitioning are available in MySQL 5.6 :
RANGE Partitioning
LIST Partitioning
COLUMNS Partitioning
HASH Partitioning
KEY Partitioning
Subpartitioning
MySQL RANGE Partitioning
In MySQL, RANGE partitioning mode allows us to specify various ranges for which data is assigned. Ranges should be contiguous but not overlapping, and are defined using the VALUES LESS THAN operator. In the following example, sale_mast table contains four columns bill_no, bill_date, cust_code and amount. This table can be partitioned by range in various of ways, depending on your requirement. Here we have used the bill_date column and decide to partition the table 4 ways by adding a PARTITION BY RANGE clause. In these partitions the range of the sale date (sale_date) are as of follow :
partition p0 ( sale between 01-01-2013 to 31-03-2013)
partition p1 ( sale between 01-04-2013 to 30-06-2013)
partition p2 ( sale between 01-07-2013 to 30-09-2013)
partition p3 ( sale between 01-10-2013 to 30-12-2013)
Let create the table :mysql> CREATE TABLE sale_mast (bill_no INT NOT NULL, bill_date TIMESTAMP NOT NULL, cust_code VARCHAR(15) NOT NULL, amount DECIMAL(8,2) NOT NULL) PARTITION BY RANGE (UNIX_TIMESTAMP(bill_date))( PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP(‘2013-04-01’)), PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP(‘2013-07-01’)), PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP(‘2013-10-01’)), PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP(‘2014-01-01’))); Query OK, 0 rows affected (1.50 sec)
Here is the partition status of sale_mast table:mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=’sale_mast’; +—————-+————+ | PARTITION_NAME | TABLE_ROWS | +—————-+————+ | p0 | 4 | | p1 | 3 | | p2 | 2 | | p3 | 0 | +—————-+————+ 4 rows in set (0.02 sec)
In the above way you can partition the table based on sale amount (amount. In these partitions the range of the sale amount (amount) are as of follow :
partition p0 ( sale amount < 100 )
partition p1 ( sale amount < 500 )
partition p2 ( sale amount <1000 )
partition p3 ( sale amount<1500 )
Let create the table :mysql> CREATE TABLE sale_mast1 (bill_no INT NOT NULL, bill_date TIMESTAMP NOT NULL, cust_codE VARCHAR(15) NOT NULL, amount INT NOT NULL) PARTITION BY RANGE (amount) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (500), PARTITION p2 VALUES LESS THAN (1000), PARTITION p3 VALUES LESS THAN (1500)); Query OK, 0 rows affected (1.34 sec)
Drop a MySQL partition
If you feel some data are useless in a partitioned table you can drop one or more partition(s). To delete all rows from partition p0 of sale_mast, you can use the following statement :MySQL> ALTER TABLE sale_mast TRUNCATE PARTITION p0; Query OK, 0 rows affected (0.49 sec) mysql> SELECT * FROM sale_mast; +———+———————+———–+——–+ | bill_no | bill_date | cust_code | amount | +———+———————+———–+——–+ | 5 | 2013-04-19 00:00:00 | C234 | 656.00 | | 6 | 2013-05-31 00:00:00 | C743 | 854.00 | | 7 | 2013-06-11 00:00:00 | C234 | 542.00 | | 8 | 2013-07-24 00:00:00 | C003 | 300.00 | | 9 | 2013-08-02 00:00:00 | C456 | 475.20 | +———+———————+———–+——–+ 5 rows in set (0.01 sec)
Here is the partition status of sale_mast after dropping the partition p0 :MySQL> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=’sale_mast’; +—————-+————+ | PARTITION_NAME | TABLE_ROWS | +—————-+————+ | p0 | 0 | | p1 | 3 | | p2 | 2 | | p3 | 0 | +—————-+————+ 4 rows in set (0.05 sec)
MySQL LIST Partitioning
List partition allows us to segment data based on a pre-defined set of values (e.g. 1, 2, 3). This is done by using PARTITION BY LIST(expr) where expr is a column value and then defining each partition by means of a VALUES IN (value_list), where value_list is a comma-separated list of integers. In MySQL 5.6, it is possible to match against only a list of integers (and possibly NULL) when partitioning by LIST. In the following example, sale_mast2 table contains four columns bill_no, bill_date, agent_code, and amount. Suppose there are 11 agents represent three cities A, B, C these can be arranged in three partitions with LIST Partitioning as follows :
City
Agent ID
A
1, 2, 3
B
4, 5, 6
C
7, 8, 9, 10, 11
Let create the table :mysql> CREATE TABLE sale_mast2 (bill_no INT NOT NULL, bill_date TIMESTAMP NOT NULL, agent_codE INT NOT NULL, amount INT NOT NULL) PARTITION BY LIST(agent_code) ( PARTITION pA VALUES IN (1,2,3), PARTITION pB VALUES IN (4,5,6), PARTITION pC VALUES IN (7,8,9,10,11)); Query OK, 0 rows affected (1.17 sec)
MySQL COLUMNS Partitioning
In COLUMNS partitioning it is possible to use multiple columns in partitioning keys. There are two types of COLUMNS partitioning :
RANGE COLUMNS partitioning
LIST COLUMNS partitioning
In addition, both RANGE COLUMNS partitioning and LIST COLUMNS partitioning support the use of non-integer columns for defining value ranges or list members. The permitted data types are shown in the following list:
Both RANGE COLUMNS partitioning and LIST COLUMNS partitioning support following data types for defining value ranges or list members.
All integer types: TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT.
DATE and DATETIME.
RANGE COLUMNS partitioning
RANGE COLUMNS partitioning is similar to range partitioning with some significant difference. RANGE COLUMNS accepts a list of one or more columns as partition keys. You can define the ranges using various columns of types (mentioned above) other than integer types.
Here is the basic syntax for creating a table partitioned by RANGE COLUMNS :
column_list is a list of one or more columns.
value_list is a list of values and must be supplied for each partition definition.
column list and in the value list defining each partition must occur in the same order
The order of the column names in the partitioning column list and the value lists do not have to be the same as the order of the table column definitions in CREATE TABLE statement.
Here is an example :mysql> CREATE TABLE table3 (col1 INT, col2 INT, col3 CHAR(5), col4 INT) PARTITION BY RANGE COLUMNS(col1, col2, col3) (PARTITION p0 VALUES LESS THAN (50, 100, ‘aaaaa’), PARTITION p1 VALUES LESS THAN (100,200,’bbbbb’), PARTITION p2 VALUES LESS THAN (150,300,’ccccc’), PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE)); Query OK, 0 rows affected (1.39 sec)
In the above example –
Table table3 contains the columns col1, col2, col3, col4
The first three columns have participated in partitioning COLUMNS clause, in the order col1, col2, col3.
Each value list used to define a partition contains 3 values in the same order and (INT, INT, CHAR(5)) form.
LIST COLUMNS partitioning
LIST COLUMNS accepts a list of one or more columns as partition keys.You can use various columns of data of types other than integer types as partitioning columns. You can use string types, DATE, and DATETIME columns
In a company there are agents in 3 cities, for sales and marketing purposes. We have organized the agents in 3 cities as shown in the following table :
City
Agent ID
A
A1, A2, A3
B
B1, B2, B3
C
C1, C2, C3, C4, C5
Let create a table with LIST COLUMNS partitioning based on the above information :mysql> CREATE TABLE salemast ( agent_id VARCHAR(15), agent_name VARCHAR(50), agent_address VARCHAR(100), city_code VARCHAR(10)) PARTITION BY LIST COLUMNS(agent_id) ( PARTITION pcity_a VALUES IN(‘A1’, ‘A2’, ‘A3’), PARTITION pcity_b VALUES IN(‘B1’, ‘B2’, ‘B3’), PARTITION pcity_c VALUES IN (‘C1’, ‘C2’, ‘C3’, ‘C4’, ‘C5’)); Query OK, 0 rows affected (1.06 sec)
You can use DATE and DATETIME columns in LIST COLUMNS partitioning, see the following example :CREATE TABLE sale_master (bill_no INT NOT NULL, bill_date DATE, cust_code VARCHAR(15) NOT NULL, amount DECIMAL(8,2) NOT NULL) PARTITION BY RANGE COLUMNS (bill_date)( PARTITION p_qtr1 VALUES LESS THAN (‘2013-04-01’), PARTITION p_qtr2 VALUES LESS THAN (‘2013-07-01’), PARTITION p_qtr3 VALUES LESS THAN (‘2013-10-01’), PARTITION p_qtr4 VALUES LESS THAN (‘2014-01-01’));
MySQL HASH Partitioning
MySQL HASH partition is used to distribute data among a predefined number of partitions on a column value or expression based on a column value. This is done by using PARTITION BY HASH(expr) clause, adding in CREATE TABLE STATEMENT. In PARTITIONS num clause, num is a positive integer represents the number of partitions of the table. The following statement creates a table that uses hashing on the studetn_id column and is divided into 4 partitions :
MySQL>CREATE TABLE student (student_id INT NOT NULL,
class VARCHAR(8), name VARCHAR(40),
date_of_admission DATE NOT NULL DEFAULT '2000-01-01')
PARTITION BY HASH(student_id)
PARTITIONS 4;
Query OK, 0 rows affected (1.43 sec)
Copy
It is also possible to make a partition based on the year in which a student was admitted. See the following statement :
MySQL> CREATE TABLE student (student_id INT NOT NULL,
class VARCHAR(8), class VARCHAR(8), name VARCHAR(40),
date_of_admission DATE NOT NULL DEFAULT '2000-01-01')
PARTITION BY HASH(YEAR(date_of_admission))
PARTITIONS 4;
Query OK, 0 rows affected (1.27 sec)
Copy
MySQL KEY Partitioning
MySQL KEY partition is a special form of HASH partition, where the hashing function for key partitioning is supplied by the MySQL server. The server employs its own internal hashing function which is based on the same algorithm as PASSWORD(). This is done by using PARTITION BY KEY, adding in CREATE TABLE STATEMENT. In KEY partitioning KEY takes only a list of zero or more column names. Any columns used as the partitioning key must comprise part or all of the table’s primary key if the table has one. If there is a primary key in a table, it is used as partitioning key when no column is specified as the partitioning key. Here is an example :MySQL> CREATE TABLE table1 ( id INT NOT NULL PRIMARY KEY, fname VARCHAR(25), lname VARCHAR(25)) PARTITION BY KEY() PARTITIONS 2; Query OK, 0 rows affected (0.84 sec)
If there is no primary key but there is a unique key in a table, then the unique key is used for the partitioning key :MySQL> CREATE TABLE table2 ( id INT NOT NULL, fname VARCHAR(25), lname VARCHAR(25), UNIQUE KEY (id)) PARTITION BY KEY() PARTITIONS 2; Query OK, 0 rows affected (0.77 sec)
MySQL Subpartitioning
Subpartitioning is a method to divide each partition further in a partitioned table. See the following CREATE TABLE statement :
CREATE TABLE table10 (BILL_NO INT, sale_date DATE, cust_code VARCHAR(15),
AMOUNT DECIMAL(8,2))
PARTITION BY RANGE(YEAR(sale_date) )
SUBPARTITION BY HASH(TO_DAYS(sale_date))
SUBPARTITIONS 4 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (2010),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
Copy
In the above statement –
The table has 4 RANGE partitions.
Each of these partitions—p0, p1, p2 and p3—is further divided into 4 subpartitions.
Therefore the entire table is divided into 4 * 4 = 16 partitions.
