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.
The ALTER TABLE command is used to change the structure of an existing table. It helps to add or delete columns, create or destroy indexes, change the type of existing columns, rename columns or the table itself. It can also be used to change the comment for the table and type of the table.
A column can be added at a specific position within a table row, using FIRST or AFTER clause. By default, the column is added at the last. You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table.
INDEX | KEY
KEY is normally a synonym for INDEX
CONSTRAINT
CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns.
PRIMARY KEY
A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If not declare MySQL declares them so implicitly. A table can have only one PRIMARY KEY.
UNIQUE
A UNIQUE index creates a constraint in which all values in the index must be distinct. An error occurs when you try to add a new row with a key value that matches an existing row.
FULLTEXT
FULLTEXT indexes are used for full-text searches. Only the MyISAM storage engine supports FULLTEXT indexes. They can be created only from CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified.
SPATIAL
SPATIAL indexes can be created on spatial data types. Spatial types are supported only for MyISAM tables and indexed columns must be declared as NOT NULL.
FOREIGN KEY
InnoDB and NDB tables support checking of foreign key constraints. The columns of the referenced table must always be explicitly named. Both ON DELETE and ON UPDATE functions on foreign keys.
CHECK
For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements other than the InnoDB and NDB engine. The CHECK clause is parsed but ignored by all storage engines.
ALGORITHM=COPY
You needed this when you are applying ALTER TABLE in earlier versions of MySQL (< 5.6) while altering a table online. This method was used to use a temporary table while altering a table.
ALGORITHM = DEFAULT
is the same a specifying no ALGORITHM clause at all.
ALGORITHM=INPLACE
The ALGORITHM=INPLACE continue the operation inside the InnoDB storage engines by using the in-place technique, and fail which are not support this features with an error.
LOCK = DEFAULT
Permit a series of coincident events i.e. reads and writes when supported. Otherwise permit concurrent reads when supported else enforce exclusive access.
LOCK = NONE
When supported, permit concurrent reads and writes else return an error message.
LOCK =SHARED
When supported, allow concurrent reads but restrict writes. Remember that writes will be blocked even if concurrent writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation. When concurrent reads are not supported an error message will be returned.
LOCK = EXCLUSIVE
This enforce exclusive access. It happens even if concurrent reads/writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation.
To rename the table from testtable to w3r1, use the following statement.
ALTER TABLE testtable RENAME w3r1;
Copy
To change column col1 from INTEGER to TINYINT NOT NULL (leaving the name the same), and to change column b from VARCHAR(15) to CHAR(25) as well as renaming it from col2 to col3, the following statement can be used.
ALTER TABLE w3r1 MODIFY col1 TINYINT NOT NULL, CHANGE col2 col3 VARCHAR(25);
Copy
To add a new TIMESTAMP column named col4, the following statement can be used.
ALTER TABLE w3r1 ADD col4 TIMESTAMP;
Copy
To add an index on column col4 and a UNIQUE index on column col1, the following statement can be used.
ALTER TABLE w3r1 ADD INDEX (col4), ADD UNIQUE (col1);
Copy
To remove column col3 from the table w3r1, the following statement can be used.
ALTER TABLE w3r1 DROP COLUMN col3;
Copy
To add a new AUTO_INCREMENT integer column named col3, the following statement can be used.
ALTER TABLE w3r1 ADD col3 INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (col3);
Copy
Here in the above example, we indexed col3 (as a PRIMARY KEY) because AUTO_INCREMENT columns must be indexed, and we declare col3 as NOT NULL because primary key columns cannot be NULL.
To change the data type of col1 into BIGINT, the following statement can be used.
ALTER TABLE w3r1 MODIFY col1 BIGINT;
Copy
If you want to include the attributes UNSIGNED DEFAULT 1 and COMMENT ‘test column’, show the below statement –
To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:
ALTER TABLE w3r1 CONVERT TO CHARACTER SET latin1;
Copy
MySQL ALTER TABLE insert column
Here is the structure of newbook_mast table.
Sample Output:+————+————–+——+—–+————+——-+ | Field | Type | Null | Key | Default | Extra | +————+————–+——+—–+————+——-+ | book_id | varchar(15) | NO | PRI | | | | book_name | varchar(50) | NO | | | | | isbn_no | varchar(15) | NO | | | | | cate_id | varchar(8) | NO | | | | | aut_id | varchar(8) | NO | | | | | pub_id | varchar(8) | NO | | | | | dt_of_pub | date | NO | | 0000-00-00 | | | pub_lang | varchar(15) | YES | | NULL | | | no_page | decimal(5,0) | NO | | 0 | | | book_price | decimal(8,2) | NO | | 0.00 | | +————+————–+——+—–+————+——-+ 10 rows in set (0.00 sec)
If you want to add a column ‘id’ of integer type in the table newbook_mast, the following statement can be used.
ALTER TABLE newbook_mast
ADD id INT;
Copy
Here is the structure of the newbook_mast after add a column id .
MySQL ALTER TABLE insert column FIRST
Here is the structure of newbook_mast table.
Sample Output:+————+————–+——+—–+————+——-+ | Field | Type | Null | Key | Default | Extra | +————+————–+——+—–+————+——-+ | book_id | varchar(15) | NO | PRI | | | | book_name | varchar(50) | NO | | | | | isbn_no | varchar(15) | NO | | | | | cate_id | varchar(8) | NO | | | | | aut_id | varchar(8) | NO | | | | | pub_id | varchar(8) | NO | | | | | dt_of_pub | date | NO | | 0000-00-00 | | | pub_lang | varchar(15) | YES | | NULL | | | no_page | decimal(5,0) | NO | | 0 | | | book_price | decimal(8,2) | NO | | 0.00 | | +————+————–+——+—–+————+——-+ 10 rows in set (0.00 sec)
If you want to insert a column id of integer type, as first column of the table newbook_mast, the following statement can be used.
ALTER TABLE newbook_mast
ADD id INT FIRST ;
Copy
Here is the structure of the newbook_mast after adding a column id at first.
MySQL ALTER TABLE to insert column AFTER a column
Here is the structure of newbook_mast table.+————+————–+——+—–+————+——-+ | Field | Type | Null | Key | Default | Extra | +————+————–+——+—–+————+——-+ | book_id | varchar(15) | NO | PRI | | | | book_name | varchar(50) | NO | | | | | isbn_no | varchar(15) | NO | | | | | cate_id | varchar(8) | NO | | | | | aut_id | varchar(8) | NO | | | | | pub_id | varchar(8) | NO | | | | | dt_of_pub | date | NO | | 0000-00-00 | | | pub_lang | varchar(15) | YES | | NULL | | | no_page | decimal(5,0) | NO | | 0 | | | book_price | decimal(8,2) | NO | | 0.00 | | +————+————–+——+—–+————+——-+ 10 rows in set (0.00 sec)
If you want to add two specific columns pub_name and pub_add after pub_id and dt_of_pub columns respectively, the following statement can be used.
ALTER TABLE newbook_mast
ADD pub_name VARCHAR(35) AFTER pub_id,
ADD pub_add VARCHAR(50) AFTER dt_of_pub;
Copy
Here is the structure of the newbook_mast after add two columns in specific position said above.
MySQL ALTER TABLE ADD INDEX.If you want to add an index named ‘cate_id’ on ‘cate_id’ column for the table ‘newbook_mast’, the following statement can be used.
ALTER TABLE newbook_mast
ADD INDEX cate_id(cate_id);
Copy
Here is the indexes for the newbook_mast table after adding an index named cate_id on cate_id column.+————–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +————–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | newbook_mast | 0 | PRIMARY | 1 | book_id | A | 0 | NULL | NULL | | BTREE | | | | newbook_mast | 1 | cate_id | 1 | cate_id | A | NULL | NULL | NULL | | BTREE | | | +————–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ 2 rows in set (0.00 sec)
MySQL ALTER TABLE ADD UNIQUE INDEX
If you want to add a UNIQUE INDEX named ‘cate_id’ on ‘cate_id’ column for the table ‘newbook_mast’, the following statement can be used.
ALTER TABLE newbook_mast
ADD UNIQUE INDEX cate_id(cate_id);
Copy
Here is the unique indexes for the newbook_mast table after adding an unique index named cate_id on cate_id column.
If you want to create a FOREIGN KEY with the combination of book_id and cate_id columns of newbook_mast table with a reference from torder table, the following statement can be used.
ALTER TABLE newbook_mast
ADD FOREIGN KEY(book_id,cate_id)
REFERENCES
torder(book_id,cateid);
Copy
Here is the details of the index of newbook_mast table.+————–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +————–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | newbook_mast | 0 | PRIMARY | 1 | book_id | A | 0 | NULL | NULL | | BTREE | | | | newbook_mast | 1 | book_id | 1 | book_id | A | NULL | NULL | NULL | | BTREE | | | | newbook_mast | 1 | book_id | 2 | cate_id | A | NULL | NULL | NULL | | BTREE | | | +————–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ 3 rows in set (0.00 sec)
MySQL ALTER TABLE ADD and DROP column, INDEX, PRIMARY KEY and FOREIGN KEY
Here is the structure and index of tstpurch and torder table.Table: tstpurch +————-+—————+——+—–+————+——-+ | Field | Type | Null | Key | Default | Extra | +————-+—————+——+—–+————+——-+ | id | int(5) | YES | | NULL | | | invoice_no | varchar(12) | NO | PRI | | | | invoice_dt | date | NO | | 0000-00-00 | | | ord_no | varchar(25) | NO | MUL | NULL | | | ord_date | date | NO | | 0000-00-00 | | | receive_dt | date | NO | | 0000-00-00 | | | book_id | varchar(8) | NO | | | | | book_name | varchar(50) | NO | | | | | pub_lang | varchar(8) | YES | | NULL | | | cate_id | varchar(8) | YES | MUL | NULL | | | receive_qty | int(5) | NO | | 0 | | | purch_price | decimal(12,2) | NO | | 0.00 | | | total_cost | decimal(12,2) | NO | | 0.00 | | +————-+—————+——+—–+————+——-+ Indexes of tstpurch table +———-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +———-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | tstpurch | 0 | PRIMARY | 1 | invoice_no | A | 0 | NULL | NULL | | BTREE | | | | tstpurch | 1 | cate_id | 1 | cate_id | A | NULL | NULL | NULL | YES | BTREE | | | | tstpurch | 1 | ord_no | 1 | ord_no | A | NULL | NULL | NULL | | BTREE | | | +———-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ Table: torder +———–+————-+——+—–+————+——-+ | Field | Type | Null | Key | Default | Extra | +———–+————-+——+—–+————+——-+ | ord_no | varchar(15) | NO | PRI | | | | ord_date | date | NO | | 0000-00-00 | | | book_id | varchar(15) | NO | MUL | | | | book_name | varchar(50) | NO | | | | | cate_id | varchar(8) | NO | | | | | pub_lang | varchar(15) | NO | | | | | ord_qty | int(5) | NO | | 0 | | +———–+————-+——+—–+————+——-+ Indexes of torder +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | torder | 0 | PRIMARY | 1 | ord_no | A | 0 | NULL | NULL | | BTREE | | | | torder | 1 | book_id | 1 | book_id | A | NULL | NULL | NULL | | BTREE | | | | torder | 1 | book_id | 2 | cate_id | A | NULL | NULL | NULL | | BTREE | | | +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
If you want to modifies the structure of ‘tstpurch’ table in the following manner –
1. Drop the ‘id’ column, existing primary key, index ‘cate_id’ and foreign key ‘ord_no’. 2. Add an integer type column ‘id’ with default value 0. 3. Add column ‘chano’ which is varchar type and size 10 and don’t accept any NULL value. 4. Add a date type column ‘chadt’. 5. Add a primary key named ‘invoice_no’ on ‘invoice_no’ column. 6. Add an index named ‘cate_id’ on ‘cate_id’ column. 7. Add a foreign key in combination of two columns ‘ord_no’ and ‘book_id’ which is referred by the same primary key of ‘torder’ table.
the following statement can be used.
ALTER TABLE tstpurch
DROP id,
ADD id int NOT NULL DEFAULT 0,
ADD chano VARCHAR(10) NOT NULL,
ADD chadt date,
DROP PRIMARY KEY,
ADD PRIMARY KEY invoice_no (invoice_no),
DROP INDEX cate_id,
ADD INDEX cate_id(cate_id),
DROP FOREIGN KEY ord_no,
ADD FOREIGN KEY(book_id,cate_id) REFERENCES
torder(book_id,cate_id);
Copy
and now, here is the structure and index of tstpurch and torder tableTable: tstpurch +————-+—————+——+—–+————+——-+ | Field | Type | Null | Key | Default | Extra | +————-+—————+——+—–+————+——-+ | invoice_no | varchar(12) | NO | PRI | | | | invoice_dt | date | NO | | 0000-00-00 | | | ord_no | varchar(25) | NO | MUL | NULL | | | ord_date | date | NO | | 0000-00-00 | | | receive_dt | date | NO | | 0000-00-00 | | | book_id | varchar(8) | NO | MUL | | | | book_name | varchar(50) | NO | | | | | pub_lang | varchar(8) | YES | | NULL | | | cate_id | varchar(8) | YES | MUL | NULL | | | receive_qty | int(5) | NO | | 0 | | | purch_price | decimal(12,2) | NO | | 0.00 | | | total_cost | decimal(12,2) | NO | | 0.00 | | | id | int(11) | NO | | 0 | | | chano | varchar(10) | NO | | NULL | | | chadt | date | YES | | NULL | | +————-+—————+——+—–+————+——-+ Indexes of tstpurch +———-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +———-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | tstpurch | 0 | PRIMARY | 1 | invoice_no | A | 0 | NULL | NULL | | BTREE | | | | tstpurch | 1 | ord_no | 1 | ord_no | A | NULL | NULL | NULL | | BTREE | | | | tstpurch | 1 | cate_id | 1 | cate_id | A | NULL | NULL | NULL | YES | BTREE | | | | tstpurch | 1 | book_id | 1 | book_id | A | NULL | NULL | NULL | | BTREE | | | | tstpurch | 1 | book_id | 2 | cate_id | A | NULL | NULL | NULL | YES | BTREE | | | +———-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ Table: torder +———–+————-+——+—–+————+——-+ | Field | Type | Null | Key | Default | Extra | +———–+————-+——+—–+————+——-+ | ord_no | varchar(15) | NO | PRI | | | | ord_date | date | NO | | 0000-00-00 | | | book_id | varchar(15) | NO | MUL | | | | book_name | varchar(50) | NO | | | | | cate_id | varchar(8) | NO | | | | | pub_lang | varchar(15) | NO | | | | | ord_qty | int(5) | NO | | 0 | | +———–+————-+——+—–+————+——-+ Indexes of torder +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | torder | 0 | PRIMARY | 1 | ord_no | A | 0 | NULL | NULL | | BTREE | | | | torder | 1 | book_id | 1 | book_id | A | NULL | NULL | NULL | | BTREE | | | | torder | 1 | book_id | 2 | cate_id | A | NULL | NULL | NULL | | BTREE | | | +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
In MySQL, an index can be created on a table when the table is created with CREATE TABLE command. Otherwise, CREATE INDEX enables to add indexes to existing tables. A multiple-column index can be created using multiple columns.
The indexes are formed by concatenating the values of the given columns.
CREATE INDEX cannot be used to create a PRIMARY KEY.
Syntax:CREATE INDEX [index name] ON [table name]([column name]);
Arguments
Name
Description
index name
Name of the index.
table name
Name of the table.
column name
Name of the column.
Example:
Code:
CREATE INDEX autid ON newauthor(aut_id);
Copy
Explanation:
The above MySQL statement will create an INDEX on ‘aut_id’ column for ‘newauthor’ table.
MySQL create UNIQUE INDEX
Create UNIQUE INDEX
Using CREATE UNIQUE INDEX, you can create an unique index in MySQL.
Example:
Code:
CREATE UNIQUE INDEX newautid ON newauthor(aut_id);
Copy
Explanation:
The above MySQL statement will create an UNIQUE INDEX on ‘aut_id’ column for ‘newauthor’ table.
MySQL create UNIQUE INDEX with index type
Create UNIQUE INDEX with index type
In MySQL, you can specify the type of the INDEX with CREATE INDEX command to set a type for the index.
Example:
Code:
CREATE UNIQUE INDEX newautid ON newauthor(aut_id) USING BTREE;
Copy
Explanation:
The above MySQL statement will create an INDEX on ‘aut_id’ column for ‘newauthor’ table by an INDEX TYPE BTREE.
MySQL CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns. The purpose of inducing constraints is to enforce the integrity of a database.
MySQL CONSTRAINTS are used to limit the type of data that can be inserted into a table.
MySQL CONSTRAINTS can be classified into two types – column level and table level.
The column level constraints can apply only to one column where as table level constraints are applied to the entire table.
MySQL CONSTRAINT is declared at the time of creating a table.
MySQL CONSTRAINTs are:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
CONSTRAINT
DESCRIPTION
NOT NULL
In MySQL NOT NULL constraint allows to specify that a column can not contain any NULL value. MySQL NOT NULL can be used to CREATE and ALTER a table.
UNIQUE
The UNIQUE constraint in MySQL does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. More than one UNIQUE column can be used in a table.
PRIMARY KEY
A PRIMARY KEY constraint for a table enforces the table to accept unique data for a specific column and this constraint creates a unique index for accessing the table faster.
FOREIGN KEY
A FOREIGN KEY in MySQL creates a link between two tables by one specific column of both tables. The specified column in one table must be a PRIMARY KEY and referred by the column of another table known as FOREIGN KEY.
CHECK
A CHECK constraint controls the values in the associated column. The CHECK constraint determines whether the value is valid or not from a logical expression.
DEFAULT
In a MySQL table, each column must contain a value ( including a NULL). While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.
Using the default value as NOT NULL, while creating a MySQL table, it can be enforced that a column in a table is not allowed to store NULL values.
If you want to create a table ‘newauthor’ where no columns are allowed to store NULL VALUES the following statement can be used.
Example:
If you want to create a table ‘newauthor’ where no columns are allowed to store NULL VALUES the following statement can be used.
CREATE TABLE IF NOT EXISTS newauthor
(aut_id varchar(8) NOT NULL,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25) NOT NULL );
Copy
Here in the above statement the constraint ‘NOT NULL’ have been used to exclude the NULL VALUE.
The following picture shows that the columns will not accept the NULL values.
MySQL CREATE TABLE to check values with CHECK CONSTRAINT
Adding a CHECK CONSTRAINT on a column of a table, you can limit the range of values allowed to be stored in that column.
Example:
If you want to create a table ‘newbook_mast’ with a PRIMARY KEY on ‘book _id’ column, a unique constraint on ‘isbn_no’ column and a set the no_page in such, that it would hold values more than zero only, the following statement can be used.
CREATE TABLE IF NOT EXISTS
newbook_mast (book_id varchar(15) NOT NULL UNIQUE,
book_name varchar(50) ,
isbn_no varchar(15) NOT NULL UNIQUE ,
cate_id varchar(8) ,
aut_id varchar(8) ,
pub_id varchar(8) ,
dt_of_pub date ,
pub_lang varchar(15) ,
no_page decimal(5,0)
CHECK(no_page>0) ,
book_price decimal(8,2) ,
PRIMARY KEY (book_id)
);
Copy
Here in the above MySQL statement will create a table ‘newbook_mast’ with a PRIMARY KEY on ‘book _id’ column, unique constraint on ‘isbn_no’ column and adding CHECK(no_page>0) will set the no_page in such, that it would hold values more than zero only.
MySQL CREATE TABLE with CHECK CONSTRAINT using IN operator
MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with IN operator.
Example:
If you want to create a table ‘newauthor’ with a PRIMARY KEY on a combination of two columns (aut_id,home_city) and checking a limit value for the column country are ‘USA’,’UK’ and ‘India’, the following statement can be used.
CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL ,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL CHECK (country IN ('USA','UK','India')),
home_city varchar(25) NOT NULL,
PRIMARY KEY (aut_id,home_city));
Copy
Here in the above MySQL statement will create a table ‘newauthor’ with a PRIMARY KEY on a combination of two columns (aut_id,home_city) and the value for the column country has been limited by using IN operator.
MySQL CREATE TABLE with CHECK CONSTRAINT and LIKE operator
MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with LIKE operator.
Example:
The MySQL statement stated below will create a table ‘newbook_mast’ with a PRIMARY KEY on ‘book_id’ column and a CHECK constraint to limit value to be stored for the column dt_of_pub along with LIKE operator and another CHECK constraint to column no_page (without using LIKE operator).
CHECK (dt_of_pub LIKE ‘–/–/—-‘) checks whether the format of the date to be stored is the column dt_of_pub is like ‘–/–/—-‘.
Here is the statement.
CREATE TABLE IF NOT EXISTS newbook_mast
( book_id varchar(15) NOT NULL UNIQUE,
book_name varchar(50) ,
isbn_no varchar(15) NOT NULL UNIQUE ,
cate_id varchar(8) ,
aut_id varchar(8) ,
pub_id varchar(8) ,
dt_of_pub date CHECK (dt_of_pub LIKE '--/--/----'),
pub_lang varchar(15) ,
no_page decimal(5,0) CHECK(no_page>0) ,
book_price decimal(8,2) ,
PRIMARY KEY (book_id) );
Copy
MySQL CREATE TABLE with AND and OR operator and CHECK CONSTRAINT
MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with AND and OR operator.
Example
The MySQL statement stated below will create a table ‘newpublisher’ with a PRIMARY KEY on ‘pub_id’ column and CHECK constraint along with AND and OR operator for country and pub_city columns.
CHECK ((country=’India’ AND pub_city=’Mumbai’) OR (country=’India’ AND pub_city=’New Delhi’)) checks whether (i)country is INDIA and pub_city is Mumbai OR (ii) country is INDIA and pub_city is New Delhi.
MySQL Manual says “The CHECK clause is parsed but ignored by all storage engines.”
Here is the statement.
CREATE TABLE IF NOT EXISTS newpublisher
(pub_id varchar(8) ,
pub_name varchar(50),
pub_city varchar(25) ,
country varchar(25) ,
country_office varchar(25) ,
no_of_branch int(3),
estd date
CHECK ((country='India' AND pub_city='Mumbai')
OR (country='India' AND pub_city='New Delhi')) ,
PRIMARY KEY (pub_id) );
Copy
MySQL UNIQUE CONSTRAINT
The UNIQUE constraint creates an index such that, all values in the index column must be unique. An error occurs when any body tries to add a new row with a key value that already exists in that row.
Example:
The MySQL statement stated below will create a table ‘newauthor’ with a column ‘aut_id’ which will store unique values only since UNIQUE (aut_id) is used.
CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL ,
aut_name varchar(50)
NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25)
NOT NULL,
UNIQUE (aut_id));
Copy
The picture below shows the structure of the table.
Example of MySQL UNIQUE CONSTRAINT check unique value
The MySQL statement stated below will create a table ‘newauthor’ with a column ‘aut_id’ which is meant to store unique values only. Notice that UNIQUE is used within the column definition
CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL UNIQUE ,
aut_name varchar(50) NOT NULL,
country varchar(25)
NOT NULL,
home_city varchar(25) NOT NULL);
Copy
The following picture shows the Structure of the Table
MySQL CREATE TABLE with DEFAULT CONSTRAINT
While creating a table, MySQL allows you assign DEFAULT CONSTRAINTS to columns. DEFAULT is used to set a default value for a column and is applied using DEFAULT default_value; where default_value is the default value set to the column.
Example:
The MySQL statement stated below will create a table ‘newpublisher’ with a PRIMARY KEY on ‘pub_id’ column, a CHECK constraint with logical operators for country and pub-city columns and a default value for pub_id, pub_name, pub_city and country columns.
The MySQL statement also sets the default value white space for pub_id, pub_name, pub_city columns and ‘India’ as a default value for a country column.
Here is the statement below.
CREATE TABLE IF NOT EXISTS newpublisher
(pub_id varchar(8) NOT NULL UNIQUE DEFAULT '' ,
pub_name varchar(50) NOT NULL DEFAULT '' ,
pub_city varchar(25) NOT NULL DEFAULT '' ,
country varchar(25) NOT NULL DEFAULT 'India',
country_office varchar(25) ,
no_of_branch int(3),
estd date
CHECK ((country='India' AND pub_city='Mumbai')
OR (country='India' AND pub_city='New Delhi')) ,
PRIMARY KEY (pub_id));
Copy
MySQL CREATE TABLE with AUTO INCREMENT
MySQL allows you to set AUTO_INCREMENT to a column. Doing so will increase the value of that column by 1 automatically, each time a new record is added.
Example:
The MySQL statement stated below will create a table ‘newauthor’ with a PRIMARY KEY on ‘id’ column and the ‘id’ column is an auto incremented field.
CREATE TABLE IF NOT EXISTS newauthor
(id int NOT NULL AUTO_INCREMENT,
aut_id varchar(8),
aut_name varchar(50),
country varchar(25),
home_city varchar(25) NOT NULL,
PRIMARY KEY (id));
Copy
MySQL PRIMARY KEY CONSTRAINT
Usually, a table has a column or combination of columns that contain values used to uniquely identify each row in the table.This column or combination of columns is called PRIMARY KEY and can be created by defining a PRIMARY KEY CONSTRAINT while creating a table. A table can have only one PRIMARY KEY. A PRIMARY KEY column cannot contain NULL values.
Example:
The MySQL statement stated below will create a table ‘newauthor’ in which PRIMARY KEY set to the column aut_id.
CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL ,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25) NOT NULL,
PRIMARY KEY (aut_id));
Copy
MySQL CREATE TABLE PRIMARY KEY CONSTRAINT on single column
In this topic, we have discussed how to set a PRIMARY KEY CONSTRAINT on a column of a table.
Example:
The MySQL statement stated below will create a table ‘newauthor’ in which PRIMARY KEY set to the aut_id column. Notice that here PRIMARY KEY keyword is used within the column definition.
CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL PRIMARY KEY,
aut_name varchar(50) NOT NULL,
country varchar(25)
NOT NULL,
home_city varchar(25) NOT NULL);
Copy
MySQL CREATE TABLE PRIMARY KEY UNIQUE CONSTRAINT
In this topic, we have discussed how to set PRIMARY KEY as well as UNIQUE constraints on columns of a table while creating a table with CREATE TABLE command.
Example:
The MySQL statement stated below will create a table ‘newauthor’ in which PRIMARY KEY is set to the aut_id column and UNIQUE is set to the home_city column.
CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL PRIMARY KEY,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25) NOT NULL UNIQUE);
Copy
MySQL CREATE TABLE PRIMARY KEY on multiple columns
MySQL allows you to set PRIMARY KEY on multiple columns of a table. Doing this allows you to work on multiple columns as a single entity set as PRIMARY KEY for a table.
Example:
The MySQL statement stated below will create a table ‘newauthor’ in which PRIMARY KEY is set with the combination of aut_id and home_city columns.
CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL ,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25) NOT NULL,
PRIMARY KEY (aut_id, home_city));
Copy
MySQL creating table with FOREIGN KEY CONSTRAINT
While creating (or modifying) a MySQL table, you can set a FOREIGN KEY CONSTRAINT to a column of the table. A foreign key is a column or combination of columns which can be used to set a link between the data in two tables. PRIMARY KEY of a table is linked to the FOREIGN KEY of another table to enhance data integrity.
A list of the columns on which FOREIGN KEY is to be set.
REFERENCES
Keyword.
primary key table
Table name which contains the PRIMARY KEY.
column list
A list of the columns on which PRIMARY KEY is set in the primary key table.
Example:
If you want to do the following tasks:
A new table ‘newbook_mast’ will be created.
The PRIMARY KEY for that table ‘newbook_mast’ is ‘book_id’.
The FOREIGN KEY for the table ‘newbook_mast’ is ‘aut_id’.
The ‘aut_id’ is the PRIMARY KEY for the table ‘newauthor’.
The FOREIGN KEY ‘aut_id’ for the table ‘newbook_mast’ points to the PRIMARY KEY ‘aut_id’ of the table ‘newauthor’.
That means the ‘aut_id’s which are present in the ‘newauthor’ table, only those authors will come to the ‘newbook_mast’ table.
Here is the MySQL statement below for the above tasks.
CREATE TABLE IF NOT EXISTS newbook_mast
(book_id varchar(15) NOT NULL PRIMARY KEY,
book_name varchar(50) ,
isbn_no varchar(15) NOT NULL ,
cate_id varchar(8) ,
aut_id varchar(8) ,
pub_id varchar(8) ,
dt_of_pub date ,
pub_lang varchar(15) ,
no_page decimal(5,0) ,
book_price decimal(8,2) ,
FOREIGN KEY (aut_id) REFERENCES newauthor(aut_id));
Copy
MySQL CREATE TABLE with FOREIGN KEY CONSTRAINT on multiple columns
MySQL allows assigning FOREIGN KEY CONSTRAINTS on multiple columns of a table. Doing this, more than one columns of a table is set with a FOREIGN KEY CONSTRAINT referenced PRIMARY KEYs belonging to different tables.
Example:
If you want to do the following tasks:
A new table ‘newpurchase’ will be created. The PRIMARY KEY for that table ‘newpurchase’ is ‘invoice_no’. The one FOREIGN KEY for the table ‘newpurchase’ is a combination of ‘ord_no’ and ‘book_id’. The another FOREIGN KEY for the table ‘newpurchase’ is ‘cate_id’. The ‘ord_no’ and ‘book_id’ combination is the PRIMARY KEY for the table ‘neworder’. The ‘cate_id’ is the PRIMARY KEY for the table ‘category’. The FOREIGN KEY ‘ord_no’ and ‘book_id’ combination for the table ‘newpurchase’, which points to the PRIMARY KEY ‘ord_no’ and ‘book_id’ combination of the table ‘neworder’. That means the distinct (‘ord_no’ and ‘book_id’) combination which are present in the in the ‘neworder’ table only those unique ‘order number’ and ‘book id’ combination will come in the ‘newpurchase’ table.
The another FOREIGN KEY ‘cate_id’ for the table ‘newpurchase’, which points to the PRIMARY KEY ‘cate_id’ of the table ‘category’. That means the ‘cate_id’ which are present in the ‘category’ table only those ‘category’ will come in the ‘newpurchase’ table.
Here is the MySQL statement below for the above tasks.
CREATE TABLE IF NOT EXISTS newpurchase
(invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
invoice_dt date ,
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,
book_id varchar(8) ,
book_name varchar(50) ,
pub_lang varchar(8) ,
cate_id varchar(8) ,
receive_qty int(5) ,
purch_price decimal(12,2) ,
total_cost decimal(12,2) ,
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES neworder(ord_no,book_id),
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id));
Copy
MySQL CREATE TABLE with FOREIGN KEY CONSTRAINT on multiple tables
MySQL allows assigning FOREIGN KEY CONSTRAINTS on multiple tables. Doing this, more than one columns of a table is set with a FOREIGN KEY CONSTRAINT referenced to PRIMARY KEYs belonging to different tables.
Example:
If you want to do the following tasks :
Aa new table ‘newbook_mast’ will be created. The PRIMARY KEY for that table ‘newbook_mast’ is ‘book_id’. The one FOREIGN KEY for the table ‘newbook_mast’ is ‘aut_id’. The another FOREIGN KEY for the table ‘newbook_mast’ is ‘pub_id’. The ‘aut_id’ is the PRIMARY KEY for the table ‘newauthor’. The ‘pub_id’ is the PRIMARY KEY for the table ‘newpublisher’. The FOREIGN KEY ‘aut_id’ for the table ‘newbook_mast’, which points to the PRIMARY KEY ‘aut_id’ of the table ‘newauthor’. That means the ‘aut_id’ which are present in the in the ‘nuwauthor’ table only those authors will come to the ‘newbook_mast’ table. The another FOREIGN KEY ‘pub_id’ for the table ‘newbook_mast’ , which points to the PRIMARY KEY ‘pub_id’ of the table ‘newpublisher’. That means the ‘pub_id’ which are present in the in the ‘newpublisher’ table only those publishers will come to the ‘newbook_mast’ table.
Here is the MySQL statement below for the above tasks.
CREATE TABLE IF NOT EXISTS
newbook_mast (book_id varchar(15) NOT NULL PRIMARY KEY,
book_name varchar(50) ,
isbn_no varchar(15) NOT NULL ,
cate_id varchar(8),
aut_id varchar(8) ,
pub_id varchar(8) ,
dt_of_pub date ,
pub_lang varchar(15) ,
no_page decimal(5,0) ,
book_price decimal(8,2) ,
INDEX (aut_id),
FOREIGN KEY(aut_id) REFERENCES newauthor(aut_id),
INDEX(pub_id),
FOREIGN KEY(pub_id) REFERENCES newpublisher(pub_id) );
Copy
MySQL CREATE TABLE with CASCADE and RESTRICT
MySQL allows creating a table with CASCADE and RESTRICT options.
CASCADE option deletes or updates the row from the parent table (containing PRIMARY KEYs), and automatically delete or update the matching rows in the child table (containing FOREIGN KEYs).
RESTRICT option bars the removal (i.e. using delete) or modification (i..e using an update) of rows from the parent table.
Example:
If you want to do the following tasks:
A new table ‘newpurchase’ will be created. The PRIMARY KEY for that table ‘newpurchase’ is ‘invoice_no’. The one FOREIGN KEY for the table ‘newpurchase’ is a combination of ‘ord_no’ and ‘book_id’. The another FOREIGN KEY for the table ‘newpurchase’ is ‘cate_id’. The ‘ord_no’ and ‘book_id’ combination is the PRIMARY KEY for the table ‘neworder’. The ‘cate_id’ is the PRIMARY KEY for the table ‘category’. The FOREIGN KEY ‘ord_no’ and ‘book_id’ combination for the table ‘newpurchase’, which points to the PRIMARY KEY ‘ord_no’ and ‘book_id’ combination of the table ‘neworder’. That means the distinct (‘ord_no’ and ‘book_id’) combination which are present in the ‘neworder’ table only those unique ‘order number’ and ‘book id’ combination will come in the ‘newpurchase’ table. The another FOREIGN KEY ‘cate_id’ for the table ‘newpurchase’ , which points to the PRIMARY KEY ‘cate_id’ of the table ‘category’. That means the ‘cate_id’ which are present in the ‘category’ table only those ‘category’ will come in the ‘newpurchase’ table. The ON UPDATE CASCADE ensures that the records inside the child table ‘newpurchase’ always points to the PRIMARY KEY inside the parent table ‘neworder’. If any record gets deleted/updated from the ‘neworder’ table MySQL handles the deletion/updating of the records from ‘newpurchase’ table. ON DELETE RESTRICT prevents a record in a parent table ‘neworder’ being deleted or altered when it is still referenced from a child table ‘newpurchase’.
Here is the MySQL statement below for the above tasks.
CREATE TABLE IF NOT EXISTS newpurchase
(invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
invoice_dt date ,
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,
book_id varchar(8) ,
book_name varchar(50) ,
pub_lang varchar(8) ,
cate_id varchar(8) ,
receive_qty int(5) ,
purch_price decimal(12,2) ,
total_cost decimal(12,2) ,
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES
neworder(ord_no,book_id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id))
Copy
MySQL CREATE TABLE with SET NULL
MySQL allows you to create a table with SET NULL option. Doing so will delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL.
You can use SET NULL for DELETE as well as UPDATE.
If SET NULL is used, you should not set NOT NULL options to the columns of the child table (containing FOREIGN KEYS).
Example:
If you want to do the following tasks:
Aa new table ‘newpurchase’ will be created. The PRIMARY KEY for that table ‘newpurchase’ is ‘invoice_no’. The one FOREIGN KEY for the table ‘newpurchase’ is a combination of ‘ord_no’ and ‘book_id’. The another FOREIGN KEY for the table ‘newpurchase’ is ‘cate_id’. The ‘ord_no’ and ‘book_id’ combination is the PRIMARY KEY for the table ‘neworder’. The ‘cate_id’ is the PRIMARY KEY for the table ‘category’. The FOREIGN KEY ‘ord_no’ and ‘book_id’ combination for the table ‘newpurchase’, which points to the PRIMARY KEY ‘ord_no’ and ‘book_id’ combination of the table ‘neworder’. That means the distinct (‘ord_no’ and ‘book_id’) combination which are present in the ‘neworder’ table only those unique ‘order number’ and ‘book id’ combination will come in the ‘newpurchase’ table.
The another FOREIGN KEY ‘cate_id’ for the table ‘newpurchase’ , which points to the PRIMARY KEY ‘cate_id’ of the table ‘category’. That means the ‘cate_id’ which are present in the ‘category’ table only those ‘category’ will come in the ‘newpurchase’ table. ON UPDATE SET NULL recurses to update the ‘newpurchase’ table it has already updated during the cascade, it acts like RESTRICT. It prevents infinite loops resulting from cascaded updates.
Here is the MySQL statement below for the above tasks.
CREATE TABLE IF NOT EXISTS newpurchase
(invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
invoice_dt date ,
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,
book_id varchar(8) ,
book_name varchar(50) ,
pub_lang varchar(8) ,
cate_id varchar(8) ,
receive_qty int(5) ,
purch_price decimal(12,2) ,
total_cost decimal(12,2) ,
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES neworder
(ord_no,book_id)
ON UPDATE CASCADE ON DELETE
SET NULL,
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id));
Copy
MySQL CREATE TABLE with NO ACTION
NO ACTION option in MySQL is equivalent to RESTRICT.
Example:
If you want to do the following tasks:
Aa new table ‘newpurchase’ will be created. The PRIMARY KEY for that table ‘newpurchase’ is ‘invoice_no’. The one FOREIGN KEY for the table ‘newpurchase’ is a combination of ‘ord_no’ and ‘book_id’. The another FOREIGN KEY for the table ‘newpurchase’ is ‘cate_id’. The ‘ord_no’ and ‘book_id’ combination is the PRIMARY KEY for the table ‘neworder’. The ‘cate_id’ is the PRIMARY KEY for the table ‘category’. The FOREIGN KEY ‘ord_no’ and ‘book_id’ combination for the table ‘newpurchase’, which points to the PRIMARY KEY ‘ord_no’ and ‘book_id’ combination of the table ‘neworder’. That means the distinct (‘ord_no’ and ‘book_id’) combination which are present in the ‘neworder’ table only those unique ‘order number’ and ‘book id’ combination will come in the ‘newpurchase’ table. The another FOREIGN KEY ‘cate_id’ for the table ‘newpurchase’ , which points to the PRIMARY KEY ‘cate_id’ of the table ‘category’. That means the ‘cate_id’ which are present in the ‘category’ table only those ‘category’ will come in the ‘newpurchase’ table. The ON DELETE NO ACTION are preventing a record in a parent ‘neworder’ being deleted or altered when it is still referenced from a child table ‘newpurchase’ .
Here is the MySQL statement below for the above tasks.
CREATE TABLE IF NOT EXISTS
newpurchase (invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
invoice_dt date ,
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,
book_id varchar(8) ,
book_name varchar(50) ,
pub_lang varchar(8) ,
cate_id varchar(8) ,
receive_qty int(5) ,
purch_price decimal(12,2) ,
total_cost decimal(12,2) ,
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES
neworder(ord_no,book_id)
ON UPDATE CASCADE ON DELETE NO ACTION,
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id));
When an amount of data is stored in an organized way, that is called a database. In computers, a database is managed by a software called Database Management System.
What is a table?
A table is a set of data values. These values are organized using vertical columns and horizontal rows. Columns are identified by their names.
Pictorial representation of a database with tables
MySQL create database
In MySQL, CREATE DATABASE statement creates a database with the given name. To use this statement, you must have the CREATE privilege for the database. You will get an error if the database exists and you did not specify IF NOT EXISTS clause.
Limits on Number of Databases: MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.
CREATE SCHEMA is a synonym for CREATE DATABASE.
Syntax:CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name [create_specification] … create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
Where
database_name is the name of the new database.
Rules for valid database names are given in MySQL language structure “Schema Object Names” section.
The CHARACTER SET clause specifies the default database character set.
Example:
The following statement creates ‘bookinfo’ database.
CREATE DATABASE bookinfo;
Copy
The database names are case sensitive under Unix but this restriction does not apply in Windows. This is also true for table names. The best practice is to use same letter case for creating a database as well as a table.
Note: A database which has just been created is not current database. The user must have to instruct to make it the current database. A database needs to be created only once but a user must have to select it each time he intends to work with that database.
MySQL: Setting the Default Database
MySQL use statement is used to change the database from default to the given database.
Syntax:
use [database_name];
MySQL show database
SHOW statement displays a list of currently existing databases on the server.
Syntax:
SHOW [expression];
Example:
The following MySQL statement will show the current database.
SHOW databases;
Copy
The list of databases shown bellow by the statement may be different to the other user’s machine. SHOW DATABASES does not show databases for those you don’t have SHOW DATABASES privilege.
Sample Output:MySQL> show databases; +——————–+ | Database | +——————–+ | information_schema | | bookinfo | | MySQL | | test | +——————–+ 4 rows in set (0.01 sec)
MySQL select database
MySQL select database statement is used to see which database is currently selected.
Syntax:
SELECT [expression];
Example:
The following MySQL statement will show the current database.
SELECT DATABASE();
Copy
Sample Output:MySQL> select database(); +————+ | database() | +————+ | bookinfo | +————+ 1 row in set (0.03 sec)
MySQL show tables statement
MySQL ‘show tables’ statement displays a list of the tables in the database in use. If there is no table in the database, it returns empty rows.
Syntax:
SHOW [expression];
Example:
The following statement displays the list of tables in the database ‘bookinfo’.
Shows the CREATE DATABASE statement that creates the given database. If the SHOW statement includes an IF NOT EXISTS clause, the output to includes such a clause. SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE.
Syntax:SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
Example:
The following statement shows the create database statement of ‘world’ database.
SHOW create database world;
Copy
Sample Output:MySQL> show create database world; +———-+——————————————————————+ | Database | Create Database | +———-+——————————————————————+ | world | CREATE DATABASE world /*!40100 DEFAULT CHARACTER SET latin1 */ | +———-+——————————————————————+ 1 row in set (0.00 sec)
Find MySQL database size
Following query show you the database size in MySQL.
Sample Output:MySQL> SELECT table_schema “Database”, SUM(data_length + index_length)/1024/1024 “Size in MB” FROM information_schema.TABLES GROUP BY table_schema; +——————–+————-+ | Database | Size in MB | +——————–+————-+ | bupf | 20.09464169 | | hr | 0.28685379 | | information_schema | 0.00976563 | | mucemppf | 4.50534534 | | MySQL | 2.43705654 | | performance_schema | 0.00000000 | | sakila | 6.57598877 | | sample | 0.73437500 | | test | 0.06250000 | | tutorial | 0.02406311 | | world | 0.43582153 | +——————–+————-+ 11 rows in set (0.17 sec)
Find all the tables in a MySQL database with specific column names in them
The following statement shows all the tables in ‘hr’ database with columns ‘name’ or ‘department_id’. At first, see the tables in ‘hr’ database.
Sample Output:MySQL> USE hr; Database changed MySQL> SHOW TABLES; +—————–+ | Tables_in_hr | +—————–+ | account | | alluser | | departments | | emp_details | | job_history | | jobs | | locations | | log_emp_details | | my_v1 | | my_v2 | | my_v3 | | my_view | | new_view | | regions | | user | +—————–+ 22 rows in set (0.00 sec)
Sample Output:MySQL> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (‘department_id’, ‘name’) AND TABLE_SCHEMA=’hr’; +————-+ | TABLE_NAME | +————-+ | departments | | job_history | | my_v2 | | my_v3 | | my_view | | user | +————-+ 7 rows in set (0.04 sec)
