little things I never knew of mysql

After many years of working with MySQL I met a group of old people.

Watching their code, I realized they where using commands that where not familiar to me. Below I will bring you a (short) list of those and what they do.

SERIAL

We all know tables need PRIMARY KEYs, Many of us prefer to use an INTEGER with AUTO INCREMENT . For example:

CREATE TABLE test1(
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  description VARCHAR(255)
);

Instead, MySQL has a short cut, the SERIAL datatype, which translates to BIGINT UNSIGNED AUTO_INCREMENT.
The following two table definitions are the same:

CREATE TABLE test2(
  id SERIAL PRIMARY KEY,
  description VARCHAR(255)
);

CREATE TABLE test2(
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
  description VARCHAR(255)
);

NOTICE! In the second table definition,there is a UNIQUE key added on top of the PRIMARY KEY. I have not checked if it actually affects performance*. Take it out later if it annoys you.
*Index has a price when inserting values.

 

USING()

A nice short cut, which will also encourage you to write primary key field names better. Imagin the following two tables:

CREATE TABLE colors(
  color_id SERIAL PRIMARY KEY,
  description VARCHAR(200)
);

CREATE TABLE cars(
  car_id SERIAL PRIMARY KEY,
  color_id BIGINT UNSIGNED NOT NULL,
  car_name VARCHAR(200)
);

How do you join them?

SELECT *
FROM cars 
JOIN colors ON cars.color_id = colors.color_id

Or, you can do:

SELECT *
FROM cars 
JOIN colors USING(color_id);

NOTICE! Field name must be same, and the data type should also be the same, since it is a foreign/primary key relationship.

What is the DEFAULT?

Let’s take the cars table from above, and make the default car color 15:

CREATE TABLE cars(
  car_id SERIAL PRIMARY KEY,
  color_id BIGINT UNSIGNED NOT NULL DEFAULT 15,
  car_name VARCHAR(200)
);

In your code you may have a reset() function that will reset a car entry info to the initial/default values. Do consider that defaults may change over time. The following is a quick way to help in this scenario, to prevent maintaining the defaults both in your code and in the DB:

UPDATE cars SET color_id=DEFAULT

Use the DEFAULT keyword to refer to the currrent default value of a column.

COALESCE(v1,v2…vn)

The COALESCE function will return the first value which is not NULL.
The following table lists 3 types of phone numbers a user may have.

CREATE TABLE user_phones(
  user_id SERIAL PRIMARY KEY,
  home_phone VARCHAR(20) NULL,
  work_phone VARCHAR(20) NULL,
  cell_phone VARCHAR(20) NULL
);

Below is a query to easilly fetch the first avaialble phone:

SELECT COALESCE(home_phone,work_phone,cell_phone) AS available_phone
FROM user_phones
WHERE user_id=192311;

NOTICE! Table structure is only for the sake of example, not necessarily the best way to store the phone data.
NOTICE! If all three columns are null, the COALESCE function will return NULL.

NULLIF(arg1,arg2)

The NULLIF function will return NULL if both args are the same, and the first arg if they are not the same. Insert the following values to table user_phones from above:

INSERT INTO user_phones (user_id,home_phone,work_phone,cell_phone) 
VALUES (NULL, '222', '111', NULL), 
       (NULL, '333', '333', NULL),
       (NULL, NULL, '111', NULL);

Now run the following query and analyze the results:

SELECT nullif(home_phone,work_phone) FROM `user_phones`;

NOTICE! Do not confuse a NULL that was returned due to fields being the same VS NULL returned as it was the first value given, and it was not equal to the second. The last result in the query above demos that.

SPACESHIP operator

Lets use the above table ‘user_phones’ for this example too. Run the following query:

SELECT home_phone 
FROM `user_phones` 
WHERE home_phone  222

You would expect the result to be two records: [333],[NULL] while in reality it will be just 333.
I wont explain in depth here, just remember that any action with NULL results in NULL. So, when the query checks if 222NULL it gets NULL, and does not return that record.
You can solve this in two main ways:

 
-- MEHhhh
SELECT home_phone 
FROM `user_phones` 
WHERE home_phone  222
      OR 
      home_phone IS NULL;

 -- somewhat nicer
SELECT home_phone 
FROM `user_phones` 
WHERE NOT(home_phone  222)

The spaceship operator makes NULL behave more as a defined value for logical
operations.

And now, a RIDDLE!

Run the queries below:

CREATE TABLE IF NOT EXISTS `riddle` (
  `oh_my` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `riddle` (`oh_my`) VALUES ('59.95');
SELECT * FROM riddle where oh_my = 59.95;
SELECT * FROM riddle;

Why is the first select not finding ANYTHING?!
Post your solution in the comments.

Thanks for reading!

Feedback is welcomed.
Post a commnt.

FIN