Необходимое и достаточное условие пересечения или не пересечения двух интервалов времени или любых других отрезков используется, чаще всего, при составлении и валидации событий в расписаниях.
begin1
, end1
- начало и конец первого интервала.begin2
, end2
- начало и конец второго интервала.begin1 <= end2 AND end1 >= begin2
SELECT *
FROM event
WHERE e.begin <= :end AND e.end >= :begin
begin1 > end2 OR end1 < begin2
SELECT *
FROM event
WHERE e.begin > :end OR e.end < :begin
SET foreign_key_checks = 0;
TRUNCATE TABLE users;
SET foreign_key_checks = 1;
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY `TIME` DESC;
SELECT * ,
DATE_ADD(
birthday,
INTERVAL IF(DAYOFYEAR(birthday) >= DAYOFYEAR(CURDATE()),
YEAR(CURDATE())-YEAR(birthday),
YEAR(CURDATE())-YEAR(birthday)+1
) YEAR
) AS `next_birthday`
FROM `users`
WHERE
`birthday` IS NOT NULL
ORDER BY DATEDIFF(CURDATE(), `next_birthday`) DESC
Функция вырезает все что находит от between_start до between_end и возвращает в виде результат. пригодится для ненормализованных данных в виде строки
CREATE FUNCTION `find_between_text`(`raw_data` TEXT, `between_start` TEXT, `between_end` TEXT)
RETURNS text CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
RETURN SUBSTRING( raw_data,
LOCATE(between_start, raw_data) + CHAR_LENGTH(between_start),
LOCATE(between_end, raw_data, LOCATE(between_start, raw_data) + CHAR_LENGTH(between_start))-(LOCATE(between_start, raw_data) + CHAR_LENGTH(between_start))
);
END
Пример использования
select find_between_text(raw_data_str,'<tag>','</tag>') as found_text from table_test
Необходимо указать критерии для поиска проблемных запросов:
SET @runTime = 2;
SET @selectPattern = "%";
SET @cUser = "user";
SET @cState = "Sleep";
-- Процедура для создания и выполнения kill-запросов
DELIMITER $$
DROP PROCEDURE IF EXISTS totalKILL $$
CREATE PROCEDURE totalKILL(runTime INT, selectPattern VARCHAR(255), cUser VARCHAR(255), cState VARCHAR(255))
BEGIN
-- Стартовые директивы
DECLARE finished INTEGER DEFAULT 0;
DECLARE oneKillQuery VARCHAR(255) DEFAULT '';
DECLARE curs CURSOR FOR SELECT kill_query FROM mysql_kill_list;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
-- Ищем необходимые PID's, формируем KILL-запросы
DROP TABLE IF EXISTS mysql_kill_list;
SET @stmt = CONCAT("CREATE TEMPORARY TABLE mysql_kill_list AS
SELECT CONCAT('KILL ', id, ';') 'kill_query'
FROM information_schema.processlist
WHERE `info` LIKE ('", selectPattern, "')
AND `time` >= ", runTime, "
AND user LIKE ('", cUser, "')
AND state LIKE ('", cState, "')");
PREPARE Q FROM @stmt;
EXECUTE Q;
DEALLOCATE PREPARE Q;
-- Выполняем каждый kill-запрос по отдельности
OPEN curs;
killQueries: LOOP
FETCH curs
INTO oneKillQuery;
IF finished = 1
THEN
LEAVE killQueries;
END IF;
SET @killQuery = oneKillQuery;
PREPARE Q FROM @killQuery;
EXECUTE Q;
DEALLOCATE PREPARE Q;
END LOOP killQueries;
CLOSE curs;
-- удаляем временную таблицу
DROP TABLE mysql_kill_list;
END $$
DELIMITER ;
-- запускаем процедуру с необходимыми параметрами
CALL totalKILL(@runTime, @selectPattern, @cUser, @cState);
-- удаляем процедуру
DROP PROCEDURE totalKILL;
Результат вынужденой работы с программами, написанными на Clarion. Конвертирование даты из формата Clarion (integer) в ISO 8601 (date/datetime)
-- MSSQL DB
DECLARE @ClarionToUnixDiff INT = 36163
DECLARE @UnixToClarionDiff INT = 4
DECLARE @ClarionDateBase DATE = '1801-01-01'
-- examples
DECLARE @unixDate DATE = '2015-11-09'
DECLARE @clarionDate INT = 78478
SELECT
CONVERT(datetime,@clarionDate-@ClarionToUnixDiff) AS UnixDateTime, -- Clarion date to ISO datetime
CONVERT(date,CONVERT(datetime,@clarionDate-@ClarionToUnixDiff)) AS UnixDate, -- Clarion date to ISO date
DATEDIFF(d, @ClarionDateBase, @unixDate)+@UnixToClarionDiff as ClarionDate -- ISO date to Clarion date
;
/*
A Clarion standard date is the number of days that have elapsed since
December 28, 1800. The range of accessible dates is from January 1, 1801
(standard date 4) to December 31, 9999 (standard date 2,994,626). Date
procedures will not return correct values outside the limits of this range.
The standard date calendar also adjusts for each leap year within the range of
accessible dates. Dividing a standard date by modulo 7 gives you the day of the
week: zero = Sunday, one = Monday, etc.
If you are working with the newer DATE type fields (introduced in MSSQL2008)
then you need to make some adjustments to the techniques shown above. You
would need to use the SQL DateDiff() function to convert those DATE fields
to 4 byte integers, so that they can be directly compared to the (adjusted by -36163)
numeric CW Date integer.
*/
Конвертация при хранении дат в int'овом формате туда и обратно.
-- Из числа в timestamp (2013-12-15 17:26:25+04)
SELECT to_timestamp(1387113985);
-- Из числа в дату (2013-12-15)
SELECT to_timestamp(1387113985)::date;
-- Из числа в произвольный формат (2013\12\15 17:26:25)
SELECT to_char(to_timestamp(1387113985), 'YYYY\MM\DD HH24:MI:SS');
-- Из timestamp с учетом временной зоны в число (1387128385)
SELECT EXTRACT(EPOCH FROM '2013-12-15 17:26:25+04' at time zone 'Europe/Moscow')::int;
Находит дублирующие записи в таблице
SELECT *
FROM `users`
WHERE CONCAT( `users`.`name` , `users`.`surname` )
IN (
SELECT CONCAT( `users`.`name` , `users`.`surname` ) AS x
FROM `users`
GROUP BY x
HAVING COUNT(x) >1
)
LIMIT 0 , 30
Даже если вы не работали с MS SQL Server, для вас не будет неожиданностью тот факт, что план выполнения хранимой процедуры (и не только ее) вычисляется лишь однажды, после чего он кешируется и используется со всеми последующими входными параметрами. Обычно это поведение - это то, чего мы ожидаем. Но иногда такое поведение приводит к тому, что план запроса, являющийся оптимальным для изначальных параметров, может оказаться не оптимальным для другого набора параметров. Как решить подобную проблему?
-- Исходная хранимая процедура
ALTER PROCEDURE [dbo].[usp_contrysearch]
@country varchar(80)
AS
SELECT p.lastname, p.dob, p.sex, c.country
FROM people p join country c
ON p.personid = c.personid
WHERE c.country = @country
GO
-- ------------------
-- 1 вариант: Использование DBCC FREEPROCCACHE
-- Выполнение этой инструкции каждый раз будет очищать кеш планов запросов.
-- Эта инструкция не очищает план нативно-скомпилированных хранимых процедур.
-- Подробнее о синтаксисе можно почитать по ссылке:
-- https://msdn.microsoft.com/en-us/library/ms174283.aspx
DBCC FREEPROCCACHE
GO
EXEC usp_countrysearch 'UK'
-- ------------------
-- 2 вариант: Использование опции WITH RECOMPILE команды EXEC.
-- При каждом запуске хранимой процедуры с данной опцией будет производиться перекомпиляция:
EXEC usp_contrysearch 'UK' WITH RECOMPILE
GO
-- ------------------
-- 3 вариант: Использование опции RECOMPILE при создании хранимой процедуры,
-- после чего все запросы на исполнение будут приводить к ее принудительной перекомпиляции
-- (даже без указания EXEC WITH RECOMPILE):
ALTER PROCEDURE [dbo].[usp_contrysearch]
@country varchar(80)
AS
SELECT p.lastname, p.dob, p.sex, c.country
FROM people p join country c
ON p.personid = c.personid
WHERE c.country = @country
OPTION (RECOMPILE)
GO
-- ------------------
-- 4 вариант: Строго говоря, этот вариант не относится к принудительной перекомпиляции.
-- Это вариант предварительной оптимизации, но я о нем все равно упомяну.
-- Если мы имеем известный набор планов для хранимой процедуры
-- и набор значений входных параметров для каждого из них, то мы можем выбрать наиболее оптимальный
-- и скомпилировать хранимую процедуру так, чтобы всегда использовался этот план:
ALTER PROCEDURE [dbo].[usp_contrysearch]
@country varchar(80)
AS
SELECT p.lastname, p.dob, p.sex, c.country
FROM people p join country c
ON p.personid = c.personid
WHERE c.country = @country
OPTION (OPTIMIZE FOR (@country = 'UK'))
-- теперь для любых входных параметров будет использоаться план как для @country = 'UK'
GO