MacLochlainns Weblog

Somebody felt that I’d short changed Oracle by providing only an example for calling a stored procedure in MySQL. So, here’s an equivalent post to the MySQL sample that works in an Oracle database with PHP.

The largest difference between the two approaches is that Oracle is transactional by default while MySQL isn’t. However, the use of savepoints and rollbacks are shown in the procedure, which are the appropriate transaction controls in Oracle.

Here are the detailed steps, even though there are other blog entries with information on related subjects.

1. Sign on as the system user, and create a new user. Users in Oracle have their own schema or work area, and they don’t require a database like MySQL or SQL Server.

SQL> CREATE USER student IDENTIFIED BY student;
SQL> GRANT CONNECT, resource, CREATE any VIEW TO student;

2. Create a create_oracle_procedure.sql file with the following contents:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

-- Conditionally drop the objects to make this rerunnable.
BEGIN
  FOR i IN (SELECT table_name
            FROM   user_tables
            WHERE  table_name IN ('A','B')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('A_SEQ','B_SEQ')) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/  
 
-- Create the tables and sequences.
CREATE TABLE a
( a_id   NUMBER CONSTRAINT a_pk PRIMARY KEY
, a_text VARCHAR2(12));
 
CREATE SEQUENCE a_seq;
 
CREATE TABLE b
( b_id   NUMBER CONSTRAINT b_pk PRIMARY KEY
, a_id   NUMBER 
, b_text CHAR(12)
, CONSTRAINT fk_a FOREIGN KEY(a_id) REFERENCES a (a_id));
 
CREATE SEQUENCE b_seq;
 
-- Create a stored procedure with IN-only (default) mode parameters.
CREATE OR REPLACE PROCEDURE double_insert
( input_a VARCHAR2, input_b VARCHAR2) IS
BEGIN
  SAVEPOINT starting_point;
  INSERT INTO a VALUES (a_seq.NEXTVAL, input_a);
  INSERT INTO b VALUES (b_seq.NEXTVAL, a_seq.CURRVAL, input_b);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO starting_point;
    RETURN;
END;
/
 
-- Define a couple local session variables.
VARIABLE text1 VARCHAR2(12)
VARIABLE text2 VARCHAR2(12)
 
-- Assign values to session variables.
BEGIN
  :text1 := 'This is one.';
  :text2 := 'This is two.';
END;
/
 
-- Call the local procedure.
EXECUTE double_insert(:text1,:text2);
 
-- Select the data set. 
SELECT * FROM a;
SELECT * FROM b;

3. Quit the session as the system user. You can simply reconnect to the new schema like this:

SQL> CONNECT student/student@orcl

4. Run the file from the relative directory where you started the sqlplus executable.

SQL> @create_oracle_procedure.sql

You see the following query results at the end of the script:

      A_ID A_TEXT
---------- ------------
         1 This IS one.
 
 
      B_ID       A_ID B_TEXT
---------- ---------- ------------
         1          1 This IS two.

5. Write the following into a call_oracle_procedure.php:

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "orcl");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Declare two variables for the test procedure call.
    $val1 = "Hello Hal!";
    $val2 = "Hello Dave!";
 
    // Set the call statement, like a SQL statement.
    $sql = "BEGIN double_insert(:a,:b); END;";
 
    // Prepare the statement and bind the two strings.
    $stmt = oci_parse($c,$sql);
 
    // Bind local variables into PHP statement.
    oci_bind_by_name($stmt, ":a", $val1);
    oci_bind_by_name($stmt, ":b", $val2);
 
    // Execute it and print success or failure message.
    if (oci_execute($stmt)) {
      print "Congrats! You've executed a Oracle stored procedure from PHP!";
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
    // Free resources.
    oci_free_statement($stmt);
    oci_close($c);
  }
?>

6. Run the call_oracle_procedure.php from the command line, like this:

php call_oracle_procedure.php

7. Now you can requery the database to see the inserts made by the PHP program.

SQL> SELECT * FROM a;
 
      A_ID A_TEXT
---------- ------------
         1 This IS one.
         2 Hello Hal!
 
SQL> SELECT * FROM b;
 
      B_ID       A_ID B_TEXT
---------- ---------- ------------
         1          1 This IS two.
         2          2 Hello Dave!

There’s now a sample file for both approaches. As always, I hope this helps some folks.

A friend, who’s a MySQL-only developer, asked me a question that I couldn’t resist sharing. He wanted to know how to simplify his PHP code by making a single call to the database for a set of inserts to multiple tables.

You can imagine my delight when I got to say a stored procedure. He said, “It’s not Oracle; that won’t work.” I said, “MySQL supports stored procedures too.” Then, he asked how do you do that. Since the reference manual is a bit sparse on that, here’s a quick example.

Here are the detailed steps, even though there are some other blog entries with more information on these topics.

1. Sign on as the root user, if you need more on that go here. Create a database – storeddb:

CREATE DATABASE storeddb;

2. Create a user and grant them permissions:

mysql> CREATE USER 'student' IDENTIFIED BY 'student';
mysql> GRANT ALL ON storeddb.* TO 'student'@'localhost' IDENTIFIED BY 'student';

3. Create a create_mysql_procedure.sql file with the following contents:

-- Select the database.
USE storeddb;
 
-- Conditionally drop the objects to make this rerunnable.
DROP TABLE IF EXISTS a;
DROP TABLE IF EXISTS b;
DROP PROCEDURE IF EXISTS double_insert;
 
-- Create the tables.
CREATE TABLE a
( a_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, a_text CHAR(12));
 
CREATE TABLE b
( b_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, a_id   INT UNSIGNED 
, b_text CHAR(12));
 
-- Reset the execution delimiter to create a stored program.
DELIMITER $$
 
-- Create a stored procedure.
CREATE PROCEDURE double_insert
( input_a CHAR(12), input_b CHAR(12))
BEGIN
  START TRANSACTION;
  INSERT INTO a VALUES (NULL, input_a);
  INSERT INTO b VALUES (NULL, last_insert_id(), input_b);
  COMMIT;
END;
$$
 
-- Reset the delimiter to the default.
DELIMITER ;
 
-- Declare a couple local session variables.
SET @text1 = 'This is one.';
SET @text2 = 'This is two.';
 
-- Call the local procedure.
CALL double_insert(@text1,@text2);
 
SELECT * FROM a;
SELECT * FROM b;

4. Quit the session as the root user.

mysql> QUIT;

5. Sign on as the student user.

C:\> mysql -ustudent -pstudent

6. As the student user, source the file. You have two ways to do that. One leverage csh/Tcsh shell syntax and the other uses Bourne, Korn, or BASH shell syntax.

6(a). The csh/Tcsh syntax:

mysql> SOURCE create_mysql_procedure.sql

6(b). The Bourne, Korn, or BASH syntax:

mysql> \. create_mysql_procedure.sql

When you source it, you should see the following. Don’t worry if you see the three warnings because when you rerun the script they won’t be there. There telling you that the tables didn’t exist to be dropped the first time.

DATABASE changed
Query OK, 0 ROWS affected, 1 warning (0.00 sec)
Query OK, 0 ROWS affected, 1 warning (0.00 sec)
Query OK, 0 ROWS affected, 1 warning (0.00 sec)
Query OK, 0 ROWS affected (0.14 sec)
Query OK, 0 ROWS affected (0.00 sec)
Query OK, 0 ROWS affected (0.08 sec)
Query OK, 0 ROWS affected (0.00 sec)
Query OK, 0 ROWS affected (0.00 sec)
Query OK, 0 ROWS affected (0.09 sec)
 
+------+--------------+
| a_id | a_text       |
+------+--------------+
|    1 | This IS one. |
+------+--------------+
1 ROW IN SET (0.00 sec)
 
+------+------+--------------+
| b_id | a_id | b_text       |
+------+------+--------------+
|    1 |    1 | This IS two. |
+------+------+--------------+
1 ROW IN SET (0.00 sec)

A quick note to those new to automatic numbering in MySQL. You use a null when you don’t want to write an override signature for the INSERT statement. You can find more on SQL Automated Numbering for Oracle, MySQL, and SQL Server in this blog post.

7. Write the following PHP program, and name it call_mysql_procedure.php. We’re going to call it from the command line, but you shouldn’t have to modify it when you call it from a browser.

<?php
  // Attempt to connect to your database.
  $c = @mysqli_connect("localhost", "student", "student", "storeddb");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Initialize a statement in the scope of the connection.
    $stmt = mysqli_stmt_init($c);
 
    // Declare two variables for the test procedure call.
    $val1 = "Hello Hal!";
    $val2 = "Hello Dave!";
 
    // Set the call statement, like a SQL statement.
    $sql = "CALL double_insert(?,?)";
 
    // Prepare the statement and bind the two strings.
    if (mysqli_stmt_prepare($stmt, $sql)) {
      mysqli_stmt_bind_param($stmt, "ss", $val1, $val2);
 
      // Execute it and print success or failure message.
      $success = mysqli_stmt_execute($stmt);
      if ($success) {
        print "Congrats! You've executed a MySQL stored procedure from PHP!";
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
  }
}
?>

8. Run the call_mysql_procedure.php from the command line, like this:

php call_mysql_procedure.php

You should see the following message:

Congrats! You've executed a MySQL stored procedure from PHP!

9. You can now connect to the MySQL database storeddb and re-query the tables a and b. You should see the following, which tells you that your PHP code worked.

mysql> SELECT * FROM a;
+------+--------------+
| a_id | a_text       |
+------+--------------+
|    1 | This IS one. |
|    2 | Hello Hal!   |
+------+--------------+
2 ROWS IN SET (0.00 sec)
 
mysql> SELECT * FROM b;
+------+------+--------------+
| b_id | a_id | b_text       |
+------+------+--------------+
|    1 |    1 | This IS two. |
|    2 |    2 | Hello Dave!  |
+------+------+--------------+
2 ROWS IN SET (0.00 sec)

I guess Alice that writing PL/SQL may actually be portable to other databases? Actually, there are some severe limits on loops in MySQL’s implementation but you can move basic stored procedures as of MySQL 5.1. Officially, MySQL 5.0 supports them.

As always, I hope this helps some folks.

About 8 months ago I published how to set up a shared network printer in Windows XP, using VMWare Fusion on a Mac OS X. Recently, I went to follow my own instructions and found they failed with an older Windows XP media – OUCH!

When I checked whether or not the version of Windows XP knew about my much newer printer, the problem became clear. If you want to set up a newer printer than Windows recognizes, you’ll need the vendors media.

Here are the steps to install a printer when Windows can’t recognize the network printer:

1. First make sure you navigate to Virtual Machine on the VMWare Fusion menu, choose CD/DVD and then Connect CD/DVD to proceed.

2. Insert the disk that came with your printer. My disk is for my HP 2420 duplex printer on Windows XP, 32-bit. If you’re installing to a 64-bit version of Windows, the installer will die and the complexity rises as you download the media from HP and manually install the driver files.

The screen shots are those for configuring a printer because I neglected to capture the driver install shots earlier and didn’t have the time to do so. When I have to setup another similar version, I’ll add those. The first screen shot after the welcome screen and choosing your language follows. Click the Next button to continue.

3. Choose the Add an additional printer on printer driver radio button, then click the Next button to proceed.

4. Choose the Connected via the network radio button, and then click the Next button to continue.

5. Choose the Basic network setup for a PC or server (recommended) radio button, and then click Next button to continue.

6. Choose a method to search by enabling the Search from a list of detected printers (recommended). This choice fails from the normal process that adds a printer because it can’t detect newer printers. Click the Next button to continue.

7. This next screen is a progress bar, and it takes enough time that you’ll notice it. Click the Next button to continue.

8. Hopefully, you’ll find your printer. Click the Yes, install this printer. Click the Next button to continue.

9. The following confirms the settings. Unless you’ve manually assigned the Network Interface Card for the printer to a fixed IP address, I’d leave these settings alone. Click the Next button to continue.

10. If you’ve manually installed the Post Script driver you may want to enable it here. As to the HP LaserJet Toolbox, I would definitely leave it alone. Click the Next button to continue.

11. This is where you can rename your printer if you have a desired name. Enter any change from the default, and then click Next to continue.

12. Generally, it’s a very bad idea to share a printer from a virtual machine instance. The only time I’d even think about it would be if I were trying to replicate a problem with a nested virtual machine. Therefore, I’d suggest you choose Not Shared, and then click Next to continue.

13. Here you can put a location in for the printer and any comment you’d like to have for it. Click the Next button to continue.

14. Everything to here as been choosing the configuration. Click the Install button to install the printer.

15. This progress bar fills four times, so take a break. When it is done, click Next to continue. Don’t click that Cancel button when it hangs for bit because it may do that. You’ll need to be patient, after all it is Microsoft’s operating system and most likely an HP driver.

16. You’ve now completed the installation, click the Finish button to complete the process.

Hopefully, this helps a couple folks that are configuring a Windows XP printer inside a 32-bit Windows XP installation.

Having gone through the old DBA training at Oracle, I really appreciated finding a nice diagram of the MySQL Database Catalog. The catalog is found in the information_schema database. If you click on the image file, it’ll take you to the site where the original Visio File is found. You can also download it from there.

What may appear as a downside of the information_schema is that you can’t grant even select privileges to external users. You’d get the following if you tried to do so as the root user.

mysql> GRANT SELECT ON information_schema.* TO myuser;
ERROR 1044 (42000): Access denied FOR USER 'root'@'localhost' TO DATABASE 'information_schema'

There’s a reason for this behavior. You already have select privileges by default because the information_schema is a query only repository and open to all users. Here’s a quick example of accessing them from inside another database, which requires that you reference the tables with the owning database name.

SELECT   t.table_name
,        c.column_name
FROM     information_schema.tables t JOIN information_schema.columns c
ON       t.table_name = c.table_name 
WHERE    t.table_schema = 'SAMPLEDB';

Hope this answers a question or two.

An interesting thing happened today, as I was explaining how you call functions with embedded DML statements. The students were stunned at seeing an ORA-06576 error for a function that they knew existed. It’s one of those imperfect error messages …

Basically, they wrote a wrapper function to a parallel enabled function, and then they tried to call it into a session level bind variable, like this:

SQL> VARIABLE verified NUMBER
SQL> CALL update_contact INTO :verified;

It failed with the following message:

CALL update_contact INTO :verified
     *
ERROR at line 1:
ORA-06576: NOT a valid FUNCTION OR PROCEDURE name

They were stunned but I was mum. I suggested that they test the call in an anonymous block program. Here’s the PL/SQL call example:

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> BEGIN
  2    IF update_contact = 1 THEN
  3      DBMS_OUTPUT.put_line('Success!');
  4    ELSE
  5      DBMS_OUTPUT.put_line('Failure!');
  6    END IF;
  7  END;
  8  /

It returns

Success!

I removed the puzzled look by explaining that while you don’t need to provide the open and close parentheses inside PL/SQL, you do generally require them in the context of a CALL statement. They’re not required for stored functions in SQL statements, but they’re required for stored instantiable object types.

Here’s the correct way call the program:

SQL> CALL update_contact() INTO :verified;
SQL> SELECT DECODE(:verified,1,'Success!','Failure!') AS answer FROM dual;

It prints this to console:

ANSWER
--------
Success!

The natural question is why don’t you just run the function as part of a query. It’s a great question because it lets me demonstrate another principle. The principle that you can’t can’t perform a DML in a query, which is abstract until you see it up front and personal.

SQL> SELECT update_contact() FROM dual;

this query raises the following exception:

SELECT update_contact() FROM dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "PLSQL.UPDATE_CONTACT", line 4

Setup Code Supplement

You can find the base code for this example here. It comes from the Oracle Database 11g PL/SQL Programming book. After you run the create_store.sql script, you’ll need to run the following:

-- Add a null allowed column for derived data.
ALTER TABLE contact ADD (full_name VARCHAR2(44));
 
-- Define a function concatenate strings.
CREATE OR REPLACE FUNCTION MERGE
( last_name     VARCHAR2
, first_name    VARCHAR2
, middle_initial VARCHAR2 )
RETURN VARCHAR2 PARALLEL_ENABLE IS
BEGIN
  RETURN last_name ||', '||first_name||' '||middle_initial;
END;
/
 
-- Define a wrapper function around the merge function.
CREATE OR REPLACE FUNCTION update_contact RETURN NUMBER IS
  ret_val NUMBER := 0; -- The default return value to false or zero.
BEGIN
  UPDATE contact c1
  SET    c1.full_name = (SELECT MERGE(c2.last_name
                                     ,c2.first_name
                                     ,c2.middle_initial)
                         FROM   contact c2
                         WHERE  c1.rowid = c2.rowid);
  IF SQL%ROWCOUNT > 0 THEN
    ret_val := 1; -- This is only reached when 1 or more rows are updated.
  END IF;
  RETURN ret_val;
END update_contact;
/

Sometimes it gets tiresome when people take shots at Oracle, MySQL, SQL Server 2008, or PostgreSQL. When I went to the MySQL for Database Administrators, the instructor mentioned a number of times how many Oracle people he’d get in his class. It was said almost as if all the Oracle customers were migrating to MySQL, which I don’t think is the case. If I’m wrong just post a comment.

After writing SQL since 1985 (IBM SQL/DS), I’ve come to appreciate a number of the features in the Oracle database. This probably makes sense because I worked there for over eight years, and probably would still be there if I hadn’t left to teach at University. I’m constantly amazed as I explore and look for points of commonality across SQL dialects and PL/SQL dialects. At least, I think it’s fair to call T-SQL stored programs and MySQL stored programs PL/SQL dialects.

This blog post is about the MERGE statement and it’s close MySQL cousin, the ON DUPLICATE KEY UPDATE statement (that only works with single row INSERT statements). I’ve recently added a post demonstrating the REPLACE INTO command syntax. You may also be interested in a newer blog post about importing external data inside a MySQL procedure with cursor loops. Upfront, I have to vote for Oracle’s MERGE statement because it’s more complete as an implementation. By the way, T-SQL supports the same MERGE syntax. They’re so exact it wasn’t worth taking the space to show the syntax for the SQL Server 2008 Express product.

Oracle’s Merge Statement

The downside of Oracle’s MERGE statement is that it takes a LOT of typing. The upside from my perspective is that it enforces that you must use the primary key column. The same is not true with the MySQL syntax.

Here’s a quick example that you can cut and paste into your environment for Oracle Database 11g or remove the Oracle specific FROM dual and it’ll run in SQL Server. You can also see how to leverage joins and imports with the MERGE statement in this more recent blog post.

-- Conditionally drop the table and sequence.
BEGIN
  FOR i IN (SELECT NULL
            FROM   user_tables
            WHERE  TABLE_NAME = 'SYSTEM_USER') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE system_user CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT NULL
            FROM   user_sequences
            WHERE  sequence_name = 'SYSTEM_USER_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE system_user_s1';
  END LOOP;
END;
/
 
-- Create the table.
CREATE TABLE system_user
( system_user_id        NUMBER        CONSTRAINT pk_su   PRIMARY KEY
, system_user_name      VARCHAR2(20)  CONSTRAINT nn_su_1 NOT NULL
, system_user_group_id  NUMBER        CONSTRAINT nn_su_2 NOT NULL
, system_user_type      NUMBER        CONSTRAINT nn_su_3 NOT NULL
, first_name            VARCHAR2(20)
, middle_name           VARCHAR2(10)
, last_name             VARCHAR2(20)
, created_by            NUMBER        CONSTRAINT nn_su_4 NOT NULL
, creation_date         DATE          CONSTRAINT nn_su_5 NOT NULL
, last_updated_by       NUMBER        CONSTRAINT nn_su_6 NOT NULL
, last_update_date      DATE          CONSTRAINT nn_su_7 NOT NULL);
 
-- Create the sequence with a default start value of 1.
CREATE SEQUENCE system_user_s1;
 
-- Insert new row.
INSERT INTO system_user
VALUES
( system_user_s1.nextval
, 'SYSADMIN'
, 1
, 1
, NULL
, NULL
, NULL
, 1
, SYSDATE - 1
, 1
, SYSDATE - 1);
 
-- Insert new or merge into existing row.
MERGE INTO system_user target
USING (SELECT   1 AS system_user_id
       ,       'SYSADMIN' AS system_user_name
       ,        1 AS system_user_group_id
       ,        1 AS system_user_type
       ,       'Samuel' AS first_name
       ,        'the' AS middle_name
       ,       'Lamanite' AS last_name
       ,        1 AS created_by
       ,        SYSDATE AS creation_date
       ,        1 AS last_updated_by
       ,        SYSDATE AS last_update_date
       FROM     dual) SOURCE
ON (target.system_user_id = SOURCE.system_user_id)
WHEN MATCHED THEN
  UPDATE SET first_name = 'Samuel'
  ,          middle_name = 'the'
  ,          last_name = 'Lamanite'
  ,          last_updated_by = 1
  ,          last_update_date = SYSDATE
WHEN NOT MATCHED THEN
  INSERT
  ( target.system_user_id
  , target.system_user_name
  , target.system_user_group_id
  , target.system_user_type
  , target.first_name
  , target.middle_name
  , target.last_name
  , target.created_by
  , target.creation_date
  , target.last_updated_by
  , target.last_update_date )  
  VALUES
  ( SOURCE.system_user_id
  , SOURCE.system_user_name
  , SOURCE.system_user_group_id
  , SOURCE.system_user_type
  , SOURCE.first_name
  , SOURCE.middle_name
  , SOURCE.last_name
  , SOURCE.created_by
  , SOURCE.creation_date
  , SOURCE.last_updated_by
  , SOURCE.last_update_date );

MySQL On Duplicate Key Update Statement

The downside of MySQL’s ON DUPLICATE KEY UPDATE statement is that it takes it lets you use an override signature on the INSERT that then creates a new row when it shouldn’t. I logged a bug when I discovered that behavior earlier today but they don’t quite agree that it’s a bug. The upside is that the typing is MUCH shorter provided you remember to use the auto increment key column in the SELECT clause.

Here’s a quick example of what not to do! The next one shows you what you should do. You can see how to tie together MySQL subroutines with an INSERT ... ON DUPLICATE KEY statement in this more recent blog entry.

-- Conditionally drop the table.
DROP TABLE IF EXISTS system_user;
 
-- Create the table.
CREATE TABLE system_user
( system_user_id              INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT
, system_user_name            CHAR(20)      NOT NULL
, system_user_group_id        INT           NOT NULL
, system_user_type            INT           NOT NULL
, first_name                  CHAR(20)
, middle_name                 CHAR(10)
, last_name                   CHAR(20)
, created_by                  INT           NOT NULL
, creation_date               DATE          NOT NULL
, last_updated_by             INT           NOT NULL
, last_update_date            DATE          NOT NULL);
 
-- Insert new row.
INSERT INTO system_user
( system_user_name
, system_user_group_id
, system_user_type
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
('SYSADMIN'
, 1
, 1
, 1
, DATE_SUB(NOW(),INTERVAL 1 DAY)
, 1
, DATE_SUB(NOW(),INTERVAL 1 DAY));
 
-- Insert new or merge into existing row.
INSERT INTO system_user
( system_user_name
, system_user_group_id
, system_user_type
, first_name
, middle_name
, last_name
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
('SYSADMIN'
, 1
, 1
,'Samuel'
,'the'
,'Lamanite'
, 1
, NOW()
, 1
, NOW())
ON DUPLICATE KEY 
UPDATE first_name = 'Samuel'
,      middle_name = 'the'
,      last_name = 'Lamanite'
,      last_updated_by = 1
,      last_update_date = UTC_DATE();

This script ends up inserting two rows when only one should be present. Why did that happen? Great question! You can fix this by adding a unique key defined for the columns that make up the natural key for the SYSTEM_USER table. As noted by a comment below, the unique key must only include columns that are NOT NULL constrained. Here’s the results without such a unique key:

mysql> SELECT * FROM system_user\G
*************************** 1. ROW ***************************
      system_user_id: 1
    system_user_name: SYSADMIN
system_user_group_id: 1
    system_user_type: 1
          first_name: NULL
         middle_name: NULL
           last_name: NULL
          created_by: 1
       creation_date: 2009-05-24
     last_updated_by: 1
    last_update_date: 2009-05-24
*************************** 2. ROW ***************************
      system_user_id: 2
    system_user_name: SYSADMIN
system_user_group_id: 1
    system_user_type: 1
          first_name: Samuel
         middle_name: NULL
           last_name: Lamanite
          created_by: 1
       creation_date: 2009-05-25
     last_updated_by: 1
    last_update_date: 2009-05-25
2 ROWS IN SET (0.02 sec)

The correct way to do this in MySQL is shown in the next example.

-- Conditionally drop table.
DROP TABLE IF EXISTS system_user;
 
-- Create table.
CREATE TABLE system_user
( system_user_id              INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT
, system_user_name            CHAR(20)      NOT NULL
, system_user_group_id        INT           NOT NULL
, system_user_type            INT           NOT NULL
, first_name                  CHAR(20)
, middle_name                 CHAR(10)
, last_name                   CHAR(20)
, created_by                  INT           NOT NULL
, creation_date               DATE          NOT NULL
, last_updated_by             INT           NOT NULL
, last_update_date            DATE          NOT NULL);
 
-- Insert new row.
INSERT INTO system_user
( system_user_name
, system_user_group_id
, system_user_type
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
('SYSADMIN'
, 1
, 1
, 1
, DATE_SUB(UTC_DATE(),INTERVAL 1 DAY)
, 1
, DATE_SUB(UTC_DATE(),INTERVAL 1 DAY));
 
-- Insert new or merge into existing row.
INSERT INTO system_user
VALUES
( 1
,'SYSADMIN'
, 1
, 1
,'Samuel'
,'the'
,'Lamanite'
, 1
, NOW()
, 1
, NOW())
ON DUPLICATE KEY
UPDATE first_name = 'Samuel'
,      middle_name = 'the'
,      last_name = 'Lamanite'
,      last_updated_by = 1
,      last_update_date = NOW();

This ensures that the auto increment column values are matched. It returns what you’d expect, a single row inserted into or updated in the table.

mysql> SELECT * FROM system_user\G
*************************** 1. ROW ***************************
      system_user_id: 1
    system_user_name: SYSADMIN
system_user_group_id: 1
    system_user_type: 1
          first_name: Samuel
         middle_name: NULL
           last_name: Lamanite
          created_by: 1
       creation_date: 2009-05-24
     last_updated_by: 1
    last_update_date: 2009-05-25
1 ROW IN SET (0.00 sec)

I hope this saves somebody from an insertion anomaly.

Installing MySQL is pretty straightforward, especially on a Windows operating system. However, recently I was surprised to find out that there were folks who didn’t know you could install multiple version on their Windows development machine. Here’s are the step-by-step instructions to do so. At the end of the screen shots you’ll find out how to sign-on and create your first database and user account. Naturally, it’s all at the command line because they work on any platform the same way.

1. After you download an Microsoft Software Installation (MSI) file or the zip file, you launch the installer by clicking the Next button:

2. If you want to install only one copy of the software at any given time, you can accept the default Typical radio button choice. This way, you can simply uninstall the software before you install a new version. When you want to install multiple versions on a single machine, you should check the Custom radio button. This choice lets you maintain multiple versions on your single development machine and check changes between releases. After choosing a direction, you click the Next button to continue.

3. The first thing you should do is upgrade the Developer Components. You do that by clicking on the down arrow to the right of the X

, which yields the context menu below. Place your mouse over the top most This feature will be installed on local hard drive. After that you can begin to customize the installation.

4. This is a step that you’ll see when you opt to do a Custom installation. It is important that you click the Change button and change the default directory for the installation. When you click the Change button it will launch a File Finder window.

5. You can put the files anywhere you choose but I’d recommend that you opt to use something like this directory path:

C:\Program Files\MySQL 5.1\MySQL Server 5.1\

As you’ll see in the screen shot mine is slightly different because my virtual Windows XP machine is a 64-bit operating system. That’s why you’ll see the (x86) as part of the Windows Program Files directory path. It indicates that MySQL will be installed in the 32-bit executable folder. You click the OK button to accept the chosen directory and return control back to the MySQL installation program.

6. The choice of your file directory is now displayed below the caption Install to:, and you click the Next button to continue.

7. Before you click that Next button, you have one more step if you plan on having multiple instances installed on your machine. You need to change the default storage location for data files. You do that by clicking on the MySQL Server Datafiles. When you click on that, you’ll see the following default file location below the caption Install to:

C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\

You click the Change button to enter another location for the database files.

8. After you click on Change button, you’ll see the File Finder. You can then enter the directory that you’d prefer for the installation. My suggestion is the following:

C:\Data\MySQL 5.1\MySQL Server 5.1\

Click on the OK button to complete the selection of a customized database file location.

9. You should now see the wizard screen again. Choose the Next button to cotinue.

10. This is the screen where you see what you’re about to do, before you do it. It should look like this if you’re following my instructions or like whatever you’ve opted to use in your installation. Click the Install button to begin the installation.

11. The installation starts and you’ll see a dialog with a creeping status bar. It should take about 2-3 minutes on many computers but it may seem longer.

After the status bar, you’ll see the following marketing screen. Click the Next button to continue.

12. The second marketing dialog then shows up, just click the Next button to continue.

13. The installation is now complete. You should ensure you’ve checked the Configure the MySQL Server now box. Click the Finish button to end the installation of the file system and begin the configuration of the MySQL server.

14. The next dialog is the configuration welcome. Click the Next button to continue.

15. This is an important dialog because you must choose between a Standard or Detailed configuration. You should check the Detailed radio button When you plan to install more than one MySQL version on your computer because you’ll need to control the listening port for the server. A default MySQL Sever installation sets the listening port at 3306. Typically, you’ll want the main version to run on port 3306 and set the other versions to 3307, 3308, et cetera.

16. This dialog lets you choose how to configure performance characteristics of your MySQL Server. As you become more skilled at MySQL, you’ll find that these are configurable in a file, but for now using the interface is probably your best bet. The choice you make here sets how much memory MySQL will take while running. Typically, you should choose Developer Machine unless this is a server installation or you have a log of extra memorty to dedicate to MySQL Server (that’s the mysqld process).

17. This dialog lets you configure the Inno DB files location. You should make sure that you ensure they’ll be different if you’re installing multiple releases. You click the drop down arrow for the Installation Path box, choose one and manually edit it, like shown for MySQL 5.1.

18. This dialog governs configuration parameters about concurrency. If this is for a developer machine click the Decision Support (DSS)/OLAP radio button. It generally gives you more than enough connections for development activities.

19. This is the dialog where you enable a firewall exception and set a port. Since MySQL 5.1 is my main testing environment, port 3306 (the default) is selected. You also want to enable both check boxes.

20. This dialog lets you choose a multiple byte unicode character set or the standard latin1. For those coming from an Oracle world, that’s WEISO8859-1 character set.

21. This dialog names your Microsoft Windows Service for MySQL. It is important that you change this to represent the release. The default for every release is MySQL. You should also uncheck the Include Bin Directory in Windows PATH, and set that manually in a command shell file (e.g., a mysql51.bat file). You can run the batch file when you want to work in this environment. If you allow this to append to the system path, make sure you know which one you’re using when working with MySQL.

22. This dialog sets the root password. My advise is that you leave unchecked the Create An Anonymous Account and the Enable root access from remote machines boxes. Make sure you use a password that you can remember, even if it is your personal machine.

23. You’re almost done. The next dialog confirms you want to configure MySQL. Click the Execute button to configure the server.

After the configuration, you’ll see the following dialog. It means you’re done.

24. If you encounter a failure starting the service, it’s not infrequent in any number of MSI files. I wrote another post on how to work around it here. The following is the error screen that may occur.

25. Here are the steps to create your own database and user. It also shows you how to grant privileges to the new user on only their database.

C:\Data> mysql -uroot -pcangetin -P3306

mysql> SELECT CURRENT_USER();

mysql> CREATE DATABASE sampledb;

mysql> CREATE USER 'myuser' IDENTIFIED BY 'myuser';
mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost' IDENTIFIED BY 'myuser';

mysql> QUIT;

C:\Data> mysql -umyuser -pmyuser -P3306

mysql> SELECT CURRENT_USER();

mysql> USE sampledb;

mysql> CREATE TABLE sample ( sample_id   INT PRIMARY KEY AUTO_INCREMENT
                           , sample_text VARCHAR(20));
mysql> INSERT INTO sample (sample_text) VALUES ('One'),('Two'),('Three'),('Four'),('Five');
mysql> SELECT * FROM sample;

+-----------+-------------+
| sample_id | sample_text |
+-----------+-------------+
|         1 | One         |
|         2 | Two         |
|         3 | Three       |
|         4 | Four        |
|         5 | Five        |
+-----------+-------------+

-- Open the database.
USE sampledb;
 
-- Split standard out, and write a log file to the relative directory.
TEE C:/DATA/sample.txt
 
-- Run code.
DROP TABLE IF EXISTS sample;
CREATE TABLE sample ( sample_id   INT PRIMARY KEY AUTO_INCREMENT
                    , sample_text VARCHAR(20));
INSERT INTO sample (sample_text) VALUES ('One'),('Two'),('Three'),('Four'),('Five');
SELECT * FROM sample;
 
-- Turn off tee, close file to automatic appending.
NOTEE

mysql> SOURCE C:/DATA/sample.sql

This completes the basic steps, I’ll put a link here for a cross platform comparative tutorial on client-side command line interfaces, like Oracle’s sqlplus, MySQL’s mysql, and Microsoft SQL Server 2008’s sqlcmd.

I’ve begun putting together an online database tutorial and expanded this entry and added horizontal scrolling to it. You can find the improved version of the blog post as blog page here.

Surrogate keys are interesting structures in databases. They’re essential if you want to make sure you optimize your design. They’re also very useful when you want to capture the automatic numbering value for a prior INSERT statement and reuse the automatic numbering value as the foreign key value in a subsequent statement. It was interesting to see how they’re implemented differently across Oracle, MySQL, and SQL Server while providing the same utility.

Below is a synopsis of how you implement these in Oracle, MySQL, and SQL Server.

Oracle

The first thing to qualify is that Oracle is generally always in a transactional mode. That means you don’t need to do anything special to set this example up.

Oracle doesn’t support automated numbering in tables prior to Oracle 12c. Oracle 12c introduces identity columns, and the mechanics change. However, you can use sequences to mimic automated numbering prior to Oracle 12c and without identity columns in Oracle 12c. A sequence is a structure in the database that holds a current value, increments by a fixed value – typically 1. Sequences are available in SQL and PL/SQL scopes through two pseudo columns. The pseudo columns are .nextval and .currval (note the two r’s because it’s not a stray dog).

The sequence_name.nextval call in any session places the next number from the sequence into your Personal Global Area (PGA), which is a memory context. After you’ve called the sequence into memory, you can access it again by using sequence_name.currval. The sequence only changes when you call it again with the .nextval pseudo column.

-- Conditionally drop data sturctures - tables and sequences.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
			WHERE  TABLE_NAME IN ('ONE','TWO')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINT';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
			WHERE  sequence_name IN ('ONE_S1','TWO_S1')) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create base table and sequence.
CREATE TABLE one
( one_id   INT         NOT NULL CONSTRAINT pk_one PRIMARY KEY
, one_text VARCHAR(10) NOT NULL );
 
CREATE SEQUENCE one_s1;
 
-- Create dependent table and sequence.
CREATE TABLE two
( two_id   INT         NOT NULL CONSTRAINT pk_two PRIMARY KEY
, one_id   INT         NOT NULL
, two_text VARCHAR(10) NOT NULL );
 
CREATE SEQUENCE two_s1;
 
-- Insert rows into the tables with sequence values.
INSERT INTO one VALUES (one_s1.nextval,'One!');
INSERT INTO one VALUES (one_s1.nextval,'Two!');
INSERT INTO two VALUES (two_s1.nextval, one_s1.currval,'Other Two!');
 
-- Display the values inserted with sequences.
SELECT   o.one_id
,        o.one_text
,        t.two_id
,        t.two_text
FROM     one o JOIN two t ON o.one_id = t.one_id;

If you mimic automatic numbering with database triggers, you may not have access to the .currval value for the second INSERT statement. This occurs when you provide a NULL value expecting the trigger to manage .NEXTVAL call for you.

Transactions require that you keep the primary key value for the first table in a locally scoped variable for reuse. Then, you can pass it to the next INSERT statement. You do that with the .CURRVAL value.

You can make a potentially erroneous assumption that you’re the only user updating the table. Operating under that assumption, you can query the highest sequence number from the table before an insert, add one to it, and then attempt the INSERT statement. In a multi-user system, it’s possible that somebody beats you to the finish line with their INSERT statement. Your insert would then have a duplicate surrogate key value for the one_id column, and fail on an ORA-00001 error for a uniqueness violation on a primary key column.

A database trigger can help you avoid a race condition. The trigger would ensure sequence values are unique but it may also introduce problems. A common Oracle trigger with a pseudo automatic numbering paradigm is represented by the following trigger (found in APEX generated code).

CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
BEGIN
  :NEW.one_id := one_s1.nextval;
END;
/

Caution is required on this type of automated sequence trigger. There are two problems with this type of trigger.

One scenario is where you include a call to sequence_name.NEXTVAL in your INSERT statement. It then increments the sequence, and attempts to insert the value whereupon the trigger fires and repeats the behavior. Effectively, this type of logic creates a sequence that increments by one when you submit a null value in the values clause and by two when you submit a sequence_name.NEXTVAL value.

Another scenario occurs when you attempt a bulk INSERT operation on the table. The sequence call and substitution occurs on each row of the sequence.

You face another problem when you rewrite the trigger to only fire when a surrogate primary key isn’t provided, like this:

CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
WHEN (NEW.one_id IS NULL)  -- Asynchronous with bulk insert operations when a value is provided by the bulk operation to the surrogate key column.
BEGIN
  :NEW.one_id := one_s1.nextval;
END;
/

This trigger design causes a problem only with bulk INSERT statements. Effectively, the sequence remains unaltered when you provide surrogate key values as part of inserting an array of values. The next non-bulk INSERT statement would then grab the .NEXTVAL value, attempt to use it, and raise a unique constraint violation because the bulk operation probably already used the value from the sequence.

The fix to bulk operations requires that you lock the table, disable a trigger like this, and get the .NEXTVAL value. Then, you assign the .NEXTVAL value to two local variables. One of these remains unchanged while the other increments as you populate the array for the bulk insert operation. After assigning the result from the .NEXTVAL, you drop the sequence and find the highest key value for the bulk insertion operation, add one to the highest key value, and store it in another locally stored variable. You perform the bulk insert operation and then recreate the sequence with a value one greater than the highest value in the table, which should already be in a locally scored variable. Don’t forget that you’d locked the table, so unlock it now.

You should note that database triggers run in a subshell with access only to the immediate shell that fired them. Therefore, you can’t set a bind variable in a SQL*Plus session and subsequently reference it inside the trigger body because it doesn’t have access to the variable.

MySQL

MySQL supports automatic numbering but not a default transactional mode like Oracle. You need to disable auto commit and start a transaction. You also need to assign the last automatic numbering value to a variable before using it in a subsequent INSERT statement. You must also provide an overriding list of mandatory columns when you opt to exclude the automated numbering column value. The one thing that we should all appreciate about MySQL is their desire to stay close to and comply with ANSI standards.

-- Conditionally drop the tables.
DROP TABLE IF EXISTS one;
DROP TABLE IF EXISTS two;
 
-- Create the tables with a surrogate key that automatically increments.
CREATE TABLE one ( one_id   INT PRIMARY KEY AUTO_INCREMENT
                 , one_text VARCHAR(20));
 
CREATE TABLE two ( two_id INT PRIMARY KEY AUTO_INCREMENT
                 , one_id INT
                 , two_text VARCHAR(20));
 
-- Start transaction cycle.
START TRANSACTION;
 
-- Insert first row, transfer auto increment to memory.
INSERT INTO one (one_text) VALUES ('One');
 
-- Assign last auto increment to local scope variable, the = works too.
SET @one_fk := last_insert_id();
 
-- Insert second row with auto increment and local scope variable.
INSERT INTO b (one_id, two_text) VALUES (@one_fk,'Two');
 
COMMIT;
 
-- Display the values inserted with auto incremented values.
SELECT   o.one_id
,        o.one_text
,        t.two_id
,        t.two_text
FROM     one o JOIN two t ON o.one_id = t.one_id;

SQL Server

SQL Server supports automatic numbering but they call it the identity value. There are two ways to use it but the one I’m showing is for SQL Server 2005 or newer. You can replace the older @@identity for the SCOPE_IDENTITY() function call but Microsoft has already removed first level support from SQL Server 2000. While they’ve not said @@identity is deprecated, it sure appears that’s possible in a future release.

USE student;
 
BEGIN TRAN;
 
-- Conditionally drop tables when they exist.
IF OBJECT_ID('dbo.one','U') IS NOT NULL DROP TABLE dbo.one;
IF OBJECT_ID('dbo.two','U') IS NOT NULL DROP TABLE dbo.two;
 
-- Create auto incrementing tables.
CREATE TABLE one
( one_id   INT         NOT NULL IDENTITY(1,1) CONSTRAINT pk_one PRIMARY KEY
, one_text VARCHAR(10) NOT NULL );
 
CREATE TABLE two
( two_id   INT         NOT NULL IDENTITY(1,1) CONSTRAINT pk_two PRIMARY KEY
, one_id   INT         NOT NULL
, two_text VARCHAR(10) NOT NULL );
 
-- Insert the values, and magically no override signature required.
INSERT INTO one VALUES ('One!');
INSERT INTO one VALUES ('Two!');
INSERT INTO two VALUES (SCOPE_IDENTITY(),'Other Two!');
 
-- Query the results.
SELECT   o.one_id
,        o.one_text
,        t.two_id
,        t.two_text
FROM     one o JOIN two t ON o.one_id = t.one_id;
 
COMMIT TRAN;

You should note that T-SQL doesn’t require an override signature when you use an automatic numbering column. This is different, isn’t it?

While the prior example works with two tables, it doesn’t scale to a series of tables. You should consider the following assignment pattern when you’ll have multiple last identity values in a single transaction scope.

DECLARE @one_pk AS INT;
SET @one_pk = SCOPE_IDENTITY();

As mentioned, this style is important when you’ve got a series of primary and foreign keys to map in the scope of a single transaction. Also, I’d suggest that you put all the declarations at the beginning of the transaction’s scope.

As always, I hope this helps some folks.

I finally got back to my Microsoft SQL Server 2008 Express installation for a bit of comparison documentation in the sqlcmd.exe (the client tool peer to sqlplus and mysql). After all the energy to install it, I found it didn’t work.

Update for SQL Server 2012 – it’s still broken the same way and this fix works.

When I tried to connect with:

C:\>sqlcmd.exe

It raised the following error:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific
error has occurred while establishing a connection to SQL Server. Server is not found or not
accessible. Check if instance name is correct and if SQL Server is configured to allow remote
connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

A bit of poking around yielded an answer on Chris Rasmussen’s blog and a bit more about .NET pipes on Jesse Johnston’s blog. It appears Microsoft SQL Server 2008 Express edition ships and installs with their client tool effectively shutoff.

The client executable uses a pipe to communicate to the server. You need to change the pipe from this default configuration:

\\.\pipe\MSSQL$SQLEXPRESS\sql\query

To this working version

\\.\pipe\sql\query

Then, you need to enable it and restart the service. Here are the step-by-step instructions, so you don’t have to poke around.

You’ll need to launch the SQL Server Configuration Manager to make these changes. You’ll find it by navigating to Start and then open the Microsoft SQL Server 2008 folder. You’ll then select Configuration Tools and launch SQL Server Configuration Manager, as shown in the screen shot.

Launching that provides you the following screen shot:

Open the SQL Server Network Configuration detail and choose Protocols for SQLEXPRESS. Double click on the Named Pipes protocol name.

You then enable the protocol and change the Pipe Name as noted above and shown in the screen shot below:

Click the Apply button, and the only remaining step requires you to restart the service. The easiest way (thanks to Griffth) is to click on SQL Server Services in the left dialog, then right click the SQL Server (SQLEXPRESS) service and choose Restart.

An alternative would be to open a command line window (cmd.exe) and launch the services console with the following command:

C:\> services.msc

Click on SQL Server (SQLEXPRESS), then click the Restart the services link in the middle column, as shown.

Now you should be able to launch the SQL Server 2008 Express command line console. You can get some basic help with the tool by doing the following:

C:\Data>sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 10.0.1600.22 NT INTEL X86
Copyright (c) Microsoft Corporation.  All rights reserved.
 
usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

After you’ve configured this, you can schedule routine operations, like the daily submission process in this external post.

I finally got the magic trick to find the explain plan for a query in MySQL, but chagrined to find out that you can’t explain the cost or execution of INSERT, UPDATE or DELETE statements. This blog shows you how to get the execution and execution plan for a query and the error when you try to EXPLAIN DML (Data Manipulation Language) statements.

You can see the selection type, possible indexes, chosen index, et cetera:

EXPLAIN query;

You can see the selection type, possible indexes, chosen index, et cetera plus the query execution plan with the magic EXTENDED key word:

EXPLAIN EXTENDED query;

In some cases, you may need to type SHOW ERRORS to see the execution plan. There’s no way to format it natively, like Oracle, but there may be a tool out there.

If you try to explain a DML statement, you’ll get an error message like the following:

ERROR 1064 (42000): You have an error IN your SQL syntax; CHECK the manual that corresponds TO your MySQL server version FOR the RIGHT syntax TO USE near 'UPDATE city SET District = 'Funny'' at line 1

The last rule of thumb on query execution is that you should avoid subqueries because they degrade processing speed.