How to Create a Table in MySQL Workbench

The MySQL Workbench is a useful tool with a GUI (Graphical User Interface) that is easy to use. We can recreate the tables in an entire database using the Forward Engineer option in the MySQL Workbench. In this article, we will create a table in the MySQL Workbench with step-by-step examples and screenshots.

An Introduction to the MySQL Workbench

MySQL Workbench has a list of tools that we can use to our advantage. We can do the following:

• Create and recreate databases
• Prepare ERD (Entity Relationship Diagram) models using the Create New ERD Model option
• Easily understand the database structure containing tables, primary and foreign keys, and indexes

By using the MySQL Workbench, we can get an output that allows us to recreate tables and other database objects. Using this option, we are sure that all properties of a table, including primary, foreign, and unique keys are included.

The MySQL Workbench also has robust handling for errors, allowing the user to easily copy and correct each error as they are encountered in the database.

MySQL Workbench Forward Engineer

This tool is used to create the scripts for creating and backing up a database. This can be found under the Menu, and selecting the option Database -> Forward Engineer menu. We can export database objects, as well as models, and produce outputs in various file formats including SQL files. These output files can then be applied as a script to other databases. 

Opening the MySQL Workbench

In the following example, we first create the database/schema and the tables, followed by using the Forward Engineer option to create the output script.

Upon opening the MySQL Workbench, we see the following:

Figure 1. Opening the MySQL Workbench.

Example 1: MySQL Workbench Create Schema

To create a schema using MySQL Workbench, we do the following:

1. Select File -> New Model. Then a worksheet with an automatic schema named mydb will appear. The schema in MySQL refers to the database. 

2. When the user clicks on the new model, a new database will appear with the name mydb. Double-click on the name mydb, then rename the schema to the desired database name. In this article, we create a database named library.

Figure 2. Creating a new schema in MySQL Workbench.

3. Click the Save button on the toolbar, and then save in the destination folder with the desired MySQL Workbench file name.

Example 2: Creating Tables in MySQL Workbench

We can also create tables in MySQL Workbench by double-clicking on the Add Table button. Here, we will create several tables, namely: user, book, and category table. The following are the details for each table.

user table

Column Name	Datatype
id	INT
name	VARCHAR(50)
email	VARCHAR(45)
username	VARCHAR(45)
password	VARCHAR(45)
id_book	INT

Figure 3. Creating a table user in the MySQL Workbench.

book table

Column Name	Datatype
id	INT
title	VARCHAR(50)
author	VARCHAR(20)
publisher	VARCHAR(20)
category	INT

Figure 4. Creating a table book in the MySQL Workbench.

category table

Column Name	Datatype
id	INT
name	VARCHAR(45)

Figure 5. Creating a table category in the MySQL Workbench.

Example 3: Creating MySQL Relationship Diagram

After we create tables for our database, the next step is to create a MySQL entity relationship diagram. These include relationships that exist between tables. This can also be done in the MySQL Workbench. 

In this case, we use our three tables. The following illustrates the steps that we can do in MySQL Workbench.

1. In the user table, click the Foreign Key tab to create a database relation. We can specify foreign key names, parent and child tables, and the fields. In our example, we name our foreign key fk_id_book for the column id_book in the child table user, referencing the column book in the parent table books.

Figure 6. Creating a foreign key in the MySQL Workbench.

2. We also do the same for the book table. In this example, we name our foreign key fk_id_category for the column id in the child table book, referencing the column category in the parent table category.

Figure 7. Creating a foreign key in the MySQL Workbench.

3. To display the tables and keys that we have created, in the Menu bar, we click on Model -> Add Diagram to create a new ERD (Entity Relationship Diagram) diagram. This opens up a drag-and-drop interface where we can organize our tables and have a visual representation of our database.

Figure 8. ERD (Entity Relationship Diagram) in MySQL Workbench.

4. To generate these table into a file with the ‘.sql’ extension, select File -> Export -> SQL Forward Engineer Create Script. The figure below shows us how we can do this in MySQL Workbench.

Figure 9. Using the Forward Engineer feature in MySQL Workbench.

5. The following shows us a sample output of the Forward Engineer feature in MySQL Workbench.

Figure 10. The header section of the sample output.

Figure 11. CREATE SCHEMA script (library) of sample output.

Figure 12. CREATE TABLE script (category) of sample output.

Figure 13. CREATE TABLE script (book) of the sample output.

Figure 14. CREATE TABLE script (user) of sample output.

Figure 15. The footer section of the sample output.

How to Create a Basic MySQL Table

Creating tables in databases is an important first step for storing data. The CREATE TABLE statement is rich and sometimes confusing. This tech-recipe with an example describes the basics of creating a table in MySQL.

Creating a table involves describing the columns and their attributes, whether they contain text, numbers, dates, and so on. In this tech-recipe, we will create a table to hold contact information with four columns: contact_id, name, email, and birthdate.

The contact_id column is an integer number that is 10 decimal places long; therefore, it is created with an INT(10) datatype. This column will act as the primary key for this table.

The name column holds the full name of a contact. We suppose this will be no longer than 40 characters long, so the datatype is VARCHAR(40).

The contact’s birthdate will be stored as a DATE datatype.

The following SQL command will create a table called contacts as described above:

CREATE TABLE contacts ( contact_id INT(10),
name VARCHAR(40),
birthdate DATE
);

MySQL does not care if the command includes carriage returns (thoughtfully placed, of course, like not in the middle of a keyword). If you are entering this command from the MySQL command-line interface, it will need to be terminated with a semicolon. If it is being submitted through a programming interface, as from a PHP script, the semicolon is optional. Additional details about the syntax can referenced in the official MySQL documentation.

Create a MySQL table with a primary key

A primary key uniquely identify a row in a table. One or more columns may be identified as the primary key. The values in a single column used as the primary key must be unique (like a person’s social security number). When more than one column is used, the combination of column values must be unique.

When creating the contacts table described in Create a basic MySQL table, the column contact_id can be made a primary key using PRIMARY KEY(contact_id) as with the following SQL command:

CREATE TABLE `test1` ( contact_id INT(10),
name VARCHAR(40),
birthdate DATE,
PRIMARY KEY (contact_id)
);

Additional columns can be identified as part of the primary key with a comma separated list in the PRIMARY KEY command, like PRIMARY KEY (contact_id, name).

MYSQL : SQL For Create Table Example

CREATE TABLE `table_name` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `int_field` BIGINT(20) NOT NULL DEFAULT '0',
  `string_field` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
  `long_text_field` LONGTEXT NULL COLLATE 'utf8_unicode_ci',
  `time_stamp_field` TIMESTAMP NULL DEFAULT NULL,
  `date_field` DATE NULL DEFAULT NULL,
  `date_time_field` DATETIME NULL DEFAULT NULL,
  `tinyint_field` TINYINT(1) NOT NULL DEFAULT '0',
  `double_field` DOUBLE(30,10) NULL DEFAULT '0.0000000000',
  `enum_field` ENUM('TRUE','FALSE') NULL DEFAULT 'TRUE' COLLATE 'utf8_unicode_ci',
  `set_field` SET('XA','XB','XC') NULL DEFAULT 'XA,XB' COLLATE 'utf8_unicode_ci',
  `uuid` VARCHAR(70) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uuid` (`uuid`),
  INDEX `int_field_index` (`int_field`),
  INDEX `int_field_index_complex` (`int_field`, `string_field`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

Copy a table in MySQL with CREATE TABLE LIKE

I wrote a post about how to copy a table in MySQL using the SHOW CREATE TABLE sql query. There's a much easier way to do this using CREATE TABLE LIKE, a function which was depths of my brain somewhere but I'd since forgotten. This post revises and republishes my previous post using this simpler process.

Create a copy of the table

To create a copy of a table called e.g. products and copy the data from this table into the copy the table must first be created. If we wanted to create a copy called e.g. products_bak we can use the CREATE TABLE LIKE query like so:

CREATE TABLE products_bak LIKE products

An empty copy of the table is then created. Note that if you have an auto-incremental primary key that the next value if reset to the default, usually 1.

Copying the data from the original table to the new table

Now that we've created the backup table it's simply a matter of running a INSERT INTO ... SELECT query to copy the data from the original table into the copy:

INSERT INTO products_bak SELECT * FROM products

Copying the data back again

If something went wrong with your original table after you've been mucking around with it, you can then simply delete the data from the original table using TRUNCATE and then use the same query above to copy it back again:

TRUNCATE products;
INSERT INTO products SELECT * FROM products_bak

Note that depending on the size of your table this may take some time, and it's not recommended to do it on a production website.

Future posts

Tomorrow's post will feature a PHP script to automate the process and on Wednesday I'll show how to copy a table using phpMyAdmin so you can use a GUI instead of running SQL queries.
 

Related posts:

• Show indexes for a table with MySQL
• Copy a MySQL table with phpMyAdmin
• Using SELECT REPLACE with MySQL
• Insert multiple records into MySQL with a single query
• Check if a MySQL table exists
• Using the MySQL command line tool

Example table for MySQL

In my MySQL posts I've tended to use fruit in my example tables, so in this post will standardize the table structure and data that appears in future examples. Those future posts will refer back to this post for the table structure and base data.

Table schema

Use this to create the table schema:

CREATE TABLE IF NOT EXISTS `fruit` (
  `fruit_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `variety` varchar(50) NOT NULL,
  PRIMARY KEY  (`fruit_id`)
);

I realise that this should really be normalised with two tables, one for the fruit and another for the varieties, but this simplistic non-normalised table is going to be useful for simple examples presented in future posts.

Table data

Use the following SQL to insert the data. I've mixed up the order of the names and varieties so when doing a query without ORDER BY they come out in a random order.

INSERT INTO `fruit` (`fruit_id`, `name`, `variety`) VALUES
(1, 'Apple', 'Red Delicious'),
(2, 'Pear', 'Comice'),
(3, 'Orange', 'Navel'),
(4, 'Pear', 'Bartlett'),
(5, 'Orange', 'Blood'),
(6, 'Apple', 'Cox''s Orange Pippin'),
(7, 'Apple', 'Granny Smith'),
(8, 'Pear', 'Anjou'),
(9, 'Orange', 'Valencia'),
(10, 'Banana', 'Plantain'),
(11, 'Banana', 'Burro'),
(12, 'Banana', 'Cavendish');

SELECT * FROM fruit

This is the output from SELECT * FROM fruit:

+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|        1 | Apple  | Red Delicious       |
|        2 | Pear   | Comice              |
|        3 | Orange | Navel               |
|        4 | Pear   | Bartlett            |
|        5 | Orange | Blood               |
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        8 | Pear   | Anjou               |
|        9 | Orange | Valencia            |
|       10 | Banana | Plantain            |
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
+----------+--------+---------------------+

Note that although the data is in order of the fruit_id, after doing some deletes and inserts (or truncates and re-inserting the data) it could be in any order. You can never count on the order of data from a SQL query unless ORDER BY is used.

Posts with this example

Refer to the "related posts" links below for posts using this example table. The first one will be posted this time next week, looking at how to order in a particular order that is different from the way ORDER BY would normally order it.

The subquery does not return the expected results

This is the query I have written to get the plans chosen by an user. But this is returning the records in usersubscription table even if the user is not subscribed (if there is no records in the table corresponding to the user).

$userid=$_POST['userid'];
$videoid=$_POST['videoid'];
$subscribedquery=$this->db->query("select id from usersubscription where plan_id IN
        (SELECT DISTINCT plan_id FROM subscribed_videos sv where sv.videoid = $videoid)
        OR id IN (SELECT DISTINCT assosiated_plan_id
        FROM subscription_groups sg
        JOIN subscribed_videos sv ON sv.plan_id = sg.plan_id
        WHERE sv.videoid = $videoid) and user_id=$userid");

Below I am sharing the structure of all tables.

CREATE TABLE IF NOT EXISTS `subscribed_videos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan_id` int(11) NOT NULL,
  `videoid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

INSERT INTO `subscribed_videos` (`id`, `plan_id`, `videoid`) VALUES
(7, 2, 1),
(8, 2, 2),
(14, 1, 3),
(15, 1, 4),
(16, 1, 5),
(17, 1, 21),
(18, 1, 28),
(19, 1, 2),
(20, 3, 4),
(21, 3, 6),
(24, 5, 25),
(25, 6, 5);

CREATE TABLE IF NOT EXISTS `subscription_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan_id` int(11) NOT NULL,
  `assosiated_plan_id` int(11) NOT NULL,
  `added_on` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `subscription_groups` (`id`, `plan_id`, `assosiated_plan_id`, `added_on`) VALUES
(1, 1, 1, 0),
(2, 2, 2, 0),
(3, 3, 3, 0),
(4, 4, 1, 0),
(5, 4, 2, 0),
(6, 4, 3, 0),
(12, 5, 5, 0),
(13, 5, 1, 0),
(14, 5, 2, 0),
(15, 6, 1, 0);

CREATE TABLE IF NOT EXISTS `subscription_plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan` varchar(256) NOT NULL,
  `days_limit` int(11) NOT NULL,
  `added_on` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `rate` decimal(6,2) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `subscription_plans` (`id`, `plan`, `days_limit`, `added_on`, `status`, `rate`) VALUES
(1, 'PlanA', 15, 1398249706, 1, 150.00),
(2, 'PlanB', 15, 1398249679, 1, 100.00),
(3, 'PlanC', 15, 1398249747, 1, 100.00),
(4, 'PlanD', 10, 1398249771, 1, 500.00),
(5, 'PlanE', 15, 1398250104, 1, 200.00),
(6, 'Plan R1', 20, 1398250104, 1, 200.00);

CREATE TABLE IF NOT EXISTS `usersubscription` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `subscribed_on` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `usersubscription` (`id`, `user_id`, `plan_id`, `subscribed_on`) VALUES
(1, 1, 1, 1399091458);

Content:

SELECT * FROM subscribed_videos;
+----+---------+---------+
| id | plan_id | videoid |
+----+---------+---------+
|  7 |       2 |       1 |
|  8 |       2 |       2 |
| 14 |       1 |       3 |
| 15 |       1 |       4 |
| 16 |       1 |       5 |
| 17 |       1 |      21 |
| 18 |       1 |      28 |
| 19 |       1 |       2 |
| 20 |       3 |       4 |
| 21 |       3 |       6 |
| 24 |       5 |      25 |
| 25 |       6 |       5 |
+----+---------+---------+

SELECT * FROM subscription_groups;
+----+---------+--------------------+----------+
| id | plan_id | assosiated_plan_id | added_on |
+----+---------+--------------------+----------+
|  1 |       1 |                  1 |        0 |
|  2 |       2 |                  2 |        0 |
|  3 |       3 |                  3 |        0 |
|  4 |       4 |                  1 |        0 |
|  5 |       4 |                  2 |        0 |
|  6 |       4 |                  3 |        0 |
| 12 |       5 |                  5 |        0 |
| 13 |       5 |                  1 |        0 |
| 14 |       5 |                  2 |        0 |
| 15 |       6 |                  1 |        0 |
+----+---------+--------------------+----------+

SELECT * FROM subscription_plans;
+----+---------+------------+------------+--------+--------+
| id | plan    | days_limit | added_on   | status | rate   |
+----+---------+------------+------------+--------+--------+
|  1 | PlanA   |         15 | 1398249706 |      1 | 150.00 |
|  2 | PlanB   |         15 | 1398249679 |      1 | 100.00 |
|  3 | PlanC   |         15 | 1398249747 |      1 | 100.00 |
|  4 | PlanD   |         10 | 1398249771 |      1 | 500.00 |
|  5 | PlanE   |         15 | 1398250104 |      1 | 200.00 |
|  6 | Plan R1 |         20 | 1398250104 |      1 | 200.00 |
+----+---------+------------+------------+--------+--------+

 SELECT * FROM usersubscription
+----+---------+---------+---------------+
| id | user_id | plan_id | subscribed_on |
+----+---------+---------+---------------+
|  1 |       1 |       1 |    1399091458 |
+----+---------+---------+---------------+

Fiddle of same: http://sqlfiddle.com/#!2/90b2e/1

I expect the result to be like this if the user is already subscribed to a plan of the selected video otherwise the query should return empty records:

id
---
1

Also how can I return the records only if the plan is not expired for the user using the query itself. ie, when an user purchases a plan, it will be entered in the usersubscription table. The subscribed_on field will contain the php unix time() in which it is purchased. So I would like to get only the plans corresponding to a user and a video, which is not expired, in this query. The expiry days is stored as days in days_limit field of subscription_plans table (eg: 15).

Can anyone help me to find an appropriate solution for this.

Thanks in advance.

---

I would say you should try this using joins

SELECT DISTINCT s.id ,
FROM_UNIXTIME(p.`added_on`),
DATE_ADD(FROM_UNIXTIME(s.`subscribed_on`), INTERVAL p.`days_limit` DAY) `expiry_date`
FROM usersubscription s
LEFT JOIN subscribed_videos v ON (s.plan_id = v.plan_id)
LEFT JOIN subscription_groups g ON(s.id = assosiated_plan_id )
LEFT JOIN subscribed_videos sv ON sv.plan_id = g.plan_id
LEFT JOIN `subscription_plans` p ON (p.id = s.plan_id)
WHERE s.user_id=1 AND  sv.videoid = 5
AND  v.videoid = 5
AND  DATE_ADD(FROM_UNIXTIME(s.`subscribed_on`), INTERVAL p.`days_limit` DAY) > CURRENT_DATE()

In above query i have an additional join subscription_plans to check your expiry date condition, also note you are using post variables directly in query i.e $userid=$_POST['userid'];$videoid=$_POST['videoid']; which is not safe and when you are using codeigniter then you should use active record library to build your query which will take of all escaping at its own end

Fiddle Demo

Here is the active record version of above query

$query = $this->db
    ->select('s.id')
    ->distinct()
    ->from('usersubscription s')
    ->join('subscribed_videos v ','s.plan_id = v.plan_id','LEFT')
    ->join('subscription_groups g ','s.id = assosiated_plan_id','LEFT')
    ->join('subscribed_videos sv','sv.plan_id = g.plan_id','LEFT')
    ->join('`subscription_plans` p','p.id = s.plan_id','LEFT')
    ->where('s.user_id',$userid)
    ->where('sv.videoid',$videoid)
    ->where('v.videoid',$videoid)
    ->where('DATE_ADD(FROM_UNIXTIME(s.`subscribed_on`), INTERVAL p.`days_limit` DAY) > CURRENT_DATE()',null,FALSE)
    ->get();




Mysql: Creating a table in a MySQL database

<?php
//connect to your MySQL server using your servername , username
//and password
$conn = mysql_connect("localhost" , "username" , "password");
//if you cannot connect display an error message and exit
if ($conn == false)
{
echo mysql_errno().": ".mysql_error()."<BR>";
exit;
}
//create a table called contact , this will contain 2 fields .
//fullname to store a name and email to store an email address
$query = "create table contact" .
"(fullname varchar(255), email varchar(255))";
//store this in $result variable
$result = mysql_db_query ("mydb", $query);
//if successful display this message
if ($result)
echo "Table 'mydb' was successfully created!";
//if unsuuccessful display the error message
else
echo mysql_errno().": ".mysql_error()."<BR>";
mysql_close ();
?>

Mysql: Creating a table in a MySQL

<?php

//connect to your MySQL server using your servername , username

//and password

$conn = mysql_connect("localhost" , "username" , "password");

//if you cannot connect display an error message and exit

if ($conn == false){
echo mysql_errno().": ".mysql_error()."<BR>";
exit;
}

//create a table called contact , this will contain 2 fields .

//fullname to store a name and email to store an email address

$query = "create table contact" . "(fullname varchar(255), email varchar(255))";

//store this in $result variable

$result = mysql_db_query ("mydb", $query);

//if successful display this message

if ($result)

echo "Table 'mydb' was successfully created!";

//if unsuuccessful display the error message

else

echo mysql_errno().": ".mysql_error()."<BR>";

mysql_close ();

?>

Mysql: Import data from CSV file

Table definition:

CREATE TABLE emp(
  empno INT(11) NOT NULL,
  ename VARCHAR(10) DEFAULT NULL,
  job VARCHAR(9) DEFAULT NULL,
  mgr DECIMAL(4, 0) DEFAULT NULL,
  hiredate DATE DEFAULT NULL,
  sal DECIMAL(7, 2) DEFAULT NULL,
  comm DECIMAL(7, 2) DEFAULT NULL,
  deptno INT(11) DEFAULT NULL,
  PRIMARY KEY (empno)
);

CSV-file 'emp.csv':

"empno";"ename";"job";"mgr";"hiredate";"sal";"comm";"deptno"
7369;"SMITH";"CLERK";7902;1980.12.17;800,0;null;20
7499;"ALLEN";"SALESMAN";7698;1981.02.20;1600,0;300,0;30
7521;"WARD";"SALESMAN";7698;1981.02.22;1250,0;500,0;30
7566;"JONES";"MANAGER";7839;1981.04.02;2975,0;null;20
7654;"MARTIN";"SALESMAN";7698;1981.09.28;1250,0;1400,0;30

Now, we are going to import data from 'data.csv' to 'mytable':

LOAD DATA INFILE 'emp.csv'
  INTO TABLE emp
  FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY 'rn'
  IGNORE 1 LINES;

'IGNORE 1 LINES' is used to skip table header in CSV-file. 

Check result:

SELECT * FROM emp;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800.00 |    0.00 |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |    0.00 |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

Mysql: Find and remove duplicates

Find and remove duplicates.

CREATE TABLE mytable(
  rowid INT(11) NOT NULL,
  msgid INT(11) DEFAULT NULL,
  userid INT(11) DEFAULT NULL,
  PRIMARY KEY (rowid)
);
INSERT INTO mytable VALUES 
  (1, 5, 33),
  (2, 5, 12),
  (3, 4, 21),
  (4, 5, 33),
  (5, 5, 33),
  (6, 4, 15),
  (7, 4, 21);

Select duplicates:

SELECT t1.* FROM mytable t1
  JOIN (
        SELECT msgid, userid, MIN(rowid) min_rowid FROM mytable
          GROUP BY msgid, userid
        ) t2
  ON t1.rowid <> t2.min_rowid AND t1.msgid = t2.msgid AND t1.userid = t2.userid;
+-------+-------+--------+
| rowid | msgid | userid |
+-------+-------+--------+
|     4 |     5 |     33 |
|     5 |     5 |     33 |
|     7 |     4 |     21 |
+-------+-------+--------+

Remove duplicates with a DELETE statement:

DELETE t1 FROM mytable t1
  JOIN (
        SELECT msgid, userid, MIN(rowid) min_rowid FROM mytable
          GROUP BY msgid, userid
        ) t2
  ON t1.rowid <> t2.min_rowid AND t1.msgid = t2.msgid AND t1.userid = t2.userid;

Remove duplicates with a new unique key:

ALTER IGNORE TABLE mytable
  ADD UNIQUE KEY(msgid, userid);

This ALTER TABLE with IGNORE keyword will create new unique key and remove all duplicates from the table in a one step.

SELECT * FROM mytable;
+-------+-------+--------+
| rowid | msgid | userid |
+-------+-------+--------+
|     1 |     5 |     33 |
|     2 |     5 |     12 |
|     3 |     4 |     21 |
|     6 |     4 |     15 |

Mysql: Select values that have at least ID from array

We have a table film_actor that represents many to many relationship between films and actors.

CREATE TABLE film_actor(
  film_id INT(11) NOT NULL,
  actor_id INT(11) NOT NULL,
  PRIMARY KEY (film_id, actor_id)
);
INSERT INTO film_actor VALUES 
  (1, 5),
  (1, 6),
  (1, 8),
  (1, 10),
  (2, 5),
  (2, 10),
  (2, 15),
  (3, 5),
  (3, 8),
  (3, 10),
  (4, 5),
  (4, 8);

Suppose, we want to find all films where the actors from a given array { 5, 8, 10 } were starring:

SELECT film_id FROM film_actor
GROUP BY
  film_id
HAVING 
  COUNT(IF(actor_id = 5, 1, NULL)) > 0 AND
  COUNT(IF(actor_id = 8, 1, NULL)) > 0 AND
  COUNT(IF(actor_id = 10, 1, NULL)) > 0;
+---------+
| film_id |
+---------+
|       1 |
|       3 |
+---------+

The next query will return the same films:

SELECT film_id, GROUP_CONCAT(actor_id) AS actors FROM film_actor
GROUP BY
  film_id
HAVING
  FIND_IN_SET(5, actors) AND FIND_IN_SET(8, actors) AND FIND_IN_SET(10, actors);
+---------+----------+
| film_id | actors   |
+---------+----------+
|       1 | 5,6,8,10 |
|       3 | 5,8,10   |
+---------+----------+

Mysql: Copy data from one tabe to another (update existed records)

Example shows how to update records in one table with data from another table.

CREATE TABLE table_a (
  id INT(11) DEFAULT NULL,
  column_a INT(11) DEFAULT NULL
);
CREATE TABLE table_b (
  id INT(11) DEFAULT NULL,
  column_b INT(11) DEFAULT NULL
);
INSERT INTO table_a VALUES 
  (1, 10),
  (2, 20),
  (3, 30),
  (4, NULL),
  (5, 0);
INSERT INTO table_b VALUES 
  (1, 1),
  (2, NULL),
  (3, 0),
  (4, 4),
  (5, 5);

Update data:

UPDATE table_a a
  JOIN table_b b
    ON a.id = b.id
SET
  b.column_b = a.column_a;

Result table:

SELECT * FROM table_b;
+------+----------+
| id   | column_b |
+------+----------+
|    1 |       10 |
|    2 |       20 |
|    3 |       30 |
|    4 |     NULL |
|    5 |        0 |
+------+----------+