MySQL snippets - Specific search queries and example code.

0_Search-Queries.sql

-- Find in date range
SELECT *
  FROM example
  WHERE (updated >= '1970-01-01' AND updated <= DATE_ADD('1970-01-01', INTERVAL 1 DAY))
  -- AND ...

-- Find by date format
SELECT * FROM example
  -- WHERE ...
  AND YEAR(updated_at) >= '1970'
  -- AND DATE_FORMAT(updated_at, '%Y-%m') = '1970-01'
ORDER BY id DESC
LIMIT 1

-- Find duplicates
SELECT
  COUNT(user_name), user_name
FROM example
WHERE user_name IN(
	SELECT user_name FROM example -- WHERE ...
)
GROUP BY user_name
HAVING COUNT(user_name) > 1

1_Create-DB-Tables.sql

-- ENUM
DROP TABLE IF EXISTS `example_1`;
CREATE TABLE `example_1` (
  `id` SERIAL,
  `category` ENUM ("A", "B") NOT NULL,
  `title` TINYTEXT NOT NULL,
  `name` VARCHAR(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FOREIGN KEYS
DROP TABLE IF EXISTS `example_2`;
CREATE TABLE `example_2` (
  `id` SERIAL,
  `id_a` BIGINT UNSIGNED NOT NULL,
  `is_special` TINYINT NOT NULL,
  `time_from` TIME,
  `time_to` TIME,
  `description` TEXT NOT NULL,
  UNIQUE(id_a, is_special, time_from, time_to),
  FOREIGN KEY (`id_a`) REFERENCES example_999(`id`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- CONSTRAINTS
DROP TABLE IF EXISTS `example_3`;
CREATE TABLE `example_3` (
  `id` SERIAL,
  `id_a` BIGINT UNSIGNED NOT NULL,
  `is_a` TINYINT NOT NULL,
  `is_b` TINYINT NOT NULL,
  `name` TINYTEXT NOT NULL,
  CONSTRAINT constraint_states CHECK (
    (is_a=1 AND is_b=0) OR
    (is_a=0 AND is_b IN(0, 1))
  ),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Create-DB.sql

CREATE DATABASE
  IF NOT EXISTS `example_db`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE example_db;

Transaction-Wrapper.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS = 1;

START TRANSACTION;

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;


-- -----------------------------------------------------------------------------------------------------------------------------------------

/* Own SQL ... */


-- -----------------------------------------------------------------------------------------------------------------------------------------

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

COMMIT;