Two Common MySQL Tricks: LAST_INSERT_ID() and Trash

Create a new database item, then open it for editing: LAST_INSERT_ID().

Undo is better than “are you sure”. Mark items deleted, then empty trash when sure.

Read the basics first: MySQL Install and One Table Database – SQL CRUD tutorial for Ubuntu.

Preparation

We’ll use the same database we created in MySQL Install and One Table Database – SQL CRUD tutorial for Ubuntu. If you did that tutorial, you already have the databases. Otherwise

mysql> CREATE DATABASE student CHARACTER SET utf8;
mysql> GRANT ALL ON student.* TO student@localhost IDENTIFIED BY 'dk387KaaJEE-.d//NA';
mysql> EXIT
$ mysql -u student -p
mysql> CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(1024));
mysql> INSERT INTO students(name) VALUES ("Tero");
mysql> INSERT INTO students(name) VALUES ("Matti");
mysql> INSERT INTO students(name) VALUES ("Maija");

Insert Record, Get LAST_INSERT_ID()

In a web application, you often create a new record to database, then open it to editing view. For this, you need to know the id (primary key) of the record you just inserted.

The LAST_INSERT_ID() really returns your last id, even if there are other updates to the database at the same time. The last id information is maintained per-connection basis.

mysql> INSERT INTO students(name) VALUES ("Foo");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+
1 row in set (0.01 sec)

Soft Delete, Empty Trash

Undo is better than “are you sure”.

Add a new boolean field deleted.

mysql> ALTER TABLE students ADD deleted BOOLEAN DEFAULT FALSE;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> DESCRIBE students;
+---------+---------------+------+-----+---------+----------------+
| Field   | Type          | Null | Key | Default | Extra          |
+---------+---------------+------+-----+---------+----------------+
| id      | int(11)       | NO   | PRI | NULL    | auto_increment |
| name    | varchar(1024) | YES  |     | NULL    |                |
| deleted | tinyint(1)    | YES  |     | 0       |                |
+---------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Soft delete a record.

mysql> UPDATE students SET deleted=TRUE WHERE name="Tero Karvinen";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM students;
+----+---------------+---------+
| id | name          | deleted |
+----+---------------+---------+
|  1 | Tero Karvinen |       1 |
|  3 | Maija         |       0 |
|  4 | Foo           |       0 |
+----+---------------+---------+
3 rows in set (0.00 sec)

Show only non-deleted records.

mysql> SELECT * FROM students WHERE deleted=FALSE;
+----+-------+---------+
| id | name  | deleted |
+----+-------+---------+
|  3 | Maija |       0 |
|  4 | Foo   |       0 |
+----+-------+---------+
2 rows in set (0.00 sec)

Empty trash – really, permanently delete marked records.

mysql> DELETE FROM students WHERE deleted=TRUE;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM students;
+----+-------+---------+
| id | name  | deleted |
+----+-------+---------+
|  3 | Maija |       0 |
|  4 | Foo   |       0 |
+----+-------+---------+
2 rows in set (0.00 sec)

Now you have learned two common tricks you can use in your web apps: getting last insert id and implementing trash.

Posted in Uncategorized | Tagged , , , , , , , , | Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

  • Picks

  • Boxing Clock for AndroidOcton8 Diving T-ShirtsShaking Tower Panda Android GameLearn Chinese with Android
  • Student projects