Working with MySql Delete

Intro

When we want to remove rows, we can use the DELETE statement. We can remove all rows in a table, or use selectors like WHERE and LIMIT to remove less. In this article, we will learn how to use DELETE in MySql.

Getting Setup

We will be using docker in this article, but feel free to install your database locally instead. Once you have docker installed, create a new file called docker-compose.yml and add the following.

version: '3'
 
services:
  db:
    image: mysql:latest
    container_name: db
    environment:
      MYSQL_ROOT_PASSWORD: root_pass
      MYSQL_DATABASE: app_db
      MYSQL_USER: db_user
      MYSQL_PASSWORD: db_user_pass
    ports:
      - "6033:3306"
    volumes:
      - dbdata:/var/lib/mysql
  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    container_name: pma
    links:
      - db
    environment:
      PMA_HOST: db
      PMA_PORT: 3306
      PMA_ARBITRARY: 1
    restart: always
    ports:
      - 8081:80

volumes:
  dbdata:

Next, run docker-compose up.

Now, navigate to http://localhost:8081/ to access phpMyAdmin. Then log in with the username root and pass root_pass.

Click the SQL tab and you are ready to go.

Creating a DB

In this article, we will need some data to work with. If you don't understand these commands, don't worry, we will cover them in later articles.

We will be using the sample db provided here: https://dev.mysql.com/doc/sakila/en/. However, we will only enter what we need rather than import the whole db.

With the SQL tab open (or your own sql cli going), let's first create our DB and select it.

create DATABASE if not EXISTS sakila;

USE sakila;

Next, let's create an actor table.

CREATE TABLE actor (
  actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (actor_id),
  KEY idx_actor_last_name (last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

And finally, let's enter a few rows.

INSERT INTO actor VALUES 
(1,'PENELOPE','GUINESS','2006-02-15 04:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 04:34:33'),
(3,'ED','CHASE','2006-02-15 04:34:33'),
(4,'JENNIFER','DAVIS','2006-02-15 04:34:33'),
(5,'JOHNNY','LOLLOBRIGIDA','2006-02-15 04:34:33'),
(6,'BETTE','NICHOLSON','2006-02-15 04:34:33'),
(7,'GRACE','MOSTEL','2006-02-15 04:34:33'),
(8,'MATTHEW','JOHANSSON','2006-02-15 04:34:33')

The Basic SQL

The basic sql structure as the following pattern.

DELETE FROM actor

Running this command will delete all the rows in a table. Also not, we don't include any column names, because the delete remove rows, not columns.

Specifying Rows to Delete

If we want to only delete select rows, which is more common, we can combine the DELETE statement with WHERE and LIMIT.

For example, if we want to delete a single row by id we can use the following.

DELETE FROM actor where actor_id = 1;

This will delete only one row. We can combine the DELETE command with any of our usual filters. Here is another example to delete multiple rows.

DELETE FROM actor where actor_id > 3 and actor_id < 6;

Truncate

If we have a large table, we may want to use the TRUNCATE statement. This allows MySql to skip some steps and drop the whole table. Internally MySql will drop all rows then recreate a table. This is very good when you have a large table you want to remove.

TRUNCATE actor;