I posted before on using MySQL, but I wanted to follow up, now that I’ve used the product a little more. I mostly work in SQL Server, so there are definitely a few differences to get accustomed to.
Everything I’ve listed is using the default MySQL install, version 8.0.34. I’ve created a database named TestDB. I’ll use a table named TestTable for any SQL code.
*) Database Info:
Get the MySQL version number:
SELECT VERSION();
List all of the tables in the database:
SHOW TABLES;
For the table TestTable, list the columns with data types, nullability, etc.:
DESCRIBE TestTable;
*) JDBC Driver
I’m using DBeaver to connect to MySQL, using a JDBC driver. Initially, I ran into an issue with running two queries in one batch:
SELECT * FROM TestTable; SELECT * FROM TestTable2;
“SQL 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 ‘SELECT * FROM TestTable2’ at line 2”
There’s a allowMultiQueries property for the Driver that should be set to true to allow multiple statements in one batch.
*) No Schemas:
There are no schemas or namespaces in MySQL. Two-part names are Database.Table.
SELECT * FROM TestDB.TestTable;
*) Delimiting:
Object names can be delimited with a backtick (`).
SELECT * FROM `TestTable`;
Strings can be delimited with either a single quote or a double quote.
SELECT 'Single' as Single, "Double" as `Double`;
In this case, we need to delimit the Double column alias, since it is a reserved word.
*) Joins Without JOIN: (Works in SS)
Tables can be joined using this format (This can be done with SQL Server as well)
SELECT * FROM TestTable1, TestTable2 WHERE TestTable1.id = TestTable2.id;
*) Creating Temp Tables:
Temporary tables are created with the TEMPORARY keyword:
CREATE TEMPORARY TABLE TempTable (ID int NOT NULL);
*) Identity / Auto Number Column:
Add the AUTO_INCREMENT keyword to a column definition to make it auto-numbering (IDENTITY in SQL Server).
CREATE TABLE TestTable(ID int NOT NULL AUTO_INCREMENT PRIMARY KEY);
*) Dropping and Creating tables:
We can use IF EXISTS in a drop command, like we can in SQL Server. We can also use IF NOT EXISTS when we create the table, to only create a table that doesn’t already exist:
DROP TABLE IF EXISTS TestTable; CREATE TABLE IF NOT EXISTS TestTable(ID int);
*) Data Types and Unsigned Integers:
Date and Time:
MySQL has two data types that can store a date and time combo: DateTime and TimeStamp. A datetime can store values ranging from the year 1000 until 9999. TimeStamp stores from 1970 until 2038. From what I’ve seen, Timestamp is meant to store the current date/time, like the last time that a record was edited.
Integer:
MySQL has several integer types: tinyint, smallint, int/integer, and bigint. There’s also a mediumint, which uses 3 bytes. So this will allow values up to 8,388,607.
In addition, we can use the UNSIGNED keyword in a definition for a column using an integer data type:
CREATE TABLE TestTableUnsigned (ID int UNSIGNED NOT NULL PRIMARY KEY);
This will only only allow positive integers (and zero) to be stored in this column. Plus, we can store numbers twice as high as the signed limit, since we know we won’t have negative numbers. For example, with a tiny int, we can store values from -128 to 127. However, with a unsigned tiny int, we can store from 0 to 255.
Trying to insert a negative value into an Unsigned int column will generate an error:
INSERT INTO TestTableUnsigned VALUES (-1);
Error: “SQL Error [1264] [22001]: Data truncation: Out of range value for column ‘ID’ at row 1”
Strings:
MySQL has char and varchar data types, where the max length is specified. There are also text types: TinyText, Text, MediumText, LongText. Each of these have a different max length.
*) Copy Tables:
We can use CREATE TABLE LIKE to create an empty copy of an existing table:
CREATE TABLE a LIKE b;
*) Upsert:
MySQL doesn’t use MERGE for upserts. However, we can use an INSERT with a DUPLICATE KEY UPDATE clause for an upsert. This example inserts a ‘QB’ record, then updates that records while inserting a new record.
Anton Zhiyanov has a great post on Upserts for various database systems.
DROP TABLE IF EXISTS TestTable;
CREATE TABLE TestTable(
Position varchar(10) NOT NULL PRIMARY KEY,
UpdatedAt timestamp NOT NULL,
Player varchar(20) NOT NULL
);
INSERT INTO TestTable (Position, UpdatedAt, Player)
VALUES ('QB', NOW(), 'Marcus Mariota');
INSERT INTO TestTable (Position, UpdatedAt, Player)
VALUES
('QB', NOW(), 'Desmond Ridder'),
('RB1', NOW(), 'Tyler Allgeier')
AS n
ON DUPLICATE KEY UPDATE
UpdatedAt = n.UpdatedAt,
Player = n.Player;
SELECT * FROM TestTable;
*) Quirks:
A few odd things with MySQL that I stumbled across.
Divide By Zero:
Dividing by zero returns NULL (This will raise an error in T-SQL):
SELECT 1/0;
But trying to insert the result of dividing by zero into a table will raise an error:
CREATE TABLE TestTable (ID int NOT NULL); INSERT INTO TestTable SELECT (1/0);
Error: “SQL Error [1365] [22001]: Data truncation: Division by 0”
Invalid Date:
Trying to cast a string to an invalid date will return NULL, and not raise an error as would happen in T-SQL:
SELECT CAST(2023-02-30 as Date);
Calling Functions:
I ran across an odd issue when trying to run what I thought was a valid cast:
SELECT CAST (2023-02-28 as Date);
Error: “SQL Error [1584] [42000]: Incorrect parameters in the call to stored function `CAST`”
It turns out that MySQL doesn’t like the space between the CAST and the left parenthesis. Removing that space resulted in a successful run.
Comments:
A similar issue with comments. I’m used to using double dashes for comments. But MySQL raises an error for:
--Comment
So here, MySQL wants a space after the –. So this is a valid comment:
-- Comment
These are both valid formats as well. The /* … */ format can span multiple lines, as it can in T-SQL.
#Comment /*Comment*/
This last item is very strange. You can comment out a function, but including an exclamation point will run the commented out call, in this cases returning the current date/time:
/*! SELECT NOW() */
SQL Rob 