[[:<:]], [[:>:]]
These markers stand for word boundaries.
The Spencer library supports word-beginning and word-end boundary markers ([[:<:]] and [[:>:]] notation). ICU does not. For ICU, you can use \b to match word boundaries; double the backslash because MySQL interprets it as the escape character within strings.
ALTER TABLE tablename
MODIFY COLUMN date_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
MODIFY COLUMN date_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
INSERT INTO tablename (active, user_id, value)
WITH RECURSIVE
cte AS ( SELECT 1 num
UNION ALL
SELECT num + 1 FROM cte WHERE num < 9999 )
SELECT 1, 1, num
FROM cte;
SET SESSION cte_max_recursion_depth = 10000;
INSERT INTO tablename (active, user_id, value)
SELECT 1, 1, 1 + t1.num + t2.num * 10 + t3.num * 100 + t4.num * 1000 AS value
FROM (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4;
HAVING value <= 9999;
Вопрос: как мне правильно спроектировать таблицу для связей между этими товарами. Чтобы каждый из этих товаров ссылался друг на друга?
CREATE TABLE groups_of_goods (
group_id BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (group_id, product_id),
FOREIGN KEY fk_product (product_id) REFERENCES product (product_id)
);
CREATE TABLE users (
...
phone VARCHAR(255),
....
);
ALTER TABLE users ALGORITHM = INPLACE
ADD COLUMN phone_num BIGINT UNSIGNED AS (REGEXP_REPLACE(phone, '[^0-9], ''')) VIRTUAL,
INDEX idx_phone_num (phone_num);
Error number: 1452; Symbol: ER_NO_REFERENCED_ROW_2; SQLSTATE: 23000
Message: Cannot add or update a child row: a foreign key constraint fails (%s)
InnoDB reports this error when you try to add a row but there is no parent row, and a foreign key constraint fails. Add the parent row first.
Или перед вставкой нужно создать запрос на проверку существования такой записи в связующей таблице?
Есть ли разница джоинов этих двух строк, с точки зрения нагрузки на БД?
пробовал делать это через "?" и "*" , но не получается
PreparedStatement ps = plugin.SQL.getConnection().prepareStatement("UPDATE PlayerBlocks SET ?? = ? WHERE UUID=?");
WITH cte AS (
SELECT *, SUM(rating) OVER (ORDER BY id) cum_rating
FROM table
)
SELECT
FROM cte t1
JOIN (SELECT RAND() * MAX(cum_rating) rnd_rating
FROM cte) t2 ON t2.rnd_rating BETWEEN t1.cum_rating - t1.rating AND t1.cum_rating
SELECT service.id AS service_id, COUNT(subs.id) AS c
FROM ( SELECT 13 id UNION
SELECT 74 UNION
SELECT 71 UNION
SELECT 72 ) AS service
LEFT JOIN Subscriptions subs ON subs.service_id = service.id AND subs.msisdn=992777757031
GROUP BY service.id
ORDER BY c DESC;
WITH
cte1 AS ( SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) rn
FROM t1 ),
cte2 AS ( SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) rn
FROM t2 )
SELECT cte1.id id1, cte1.name name1, cte2.id id2, cte2.name name2
FROM cte1
JOIN cte2 USING (rn, id);
SELECT u.*, t.type_name, t.description
FROM users AS u
LEFT JOIN type_user AS tu ON tu.id_user = u.id
LEFT JOIN types AS t ON tu.id_type = t.id
WHERE u.id = 1;
WITH RECURSIVE
cte1 AS ( SELECT MIN(DATE(create_date)) mindate, MAX(DATE(create_date)) maxdate
FROM shops
UNION ALL
SELECT MIN(DATE(create_date)) mindate, MAX(DATE(create_date)) maxdate
FROM users ),
cte2 AS ( SELECT MIN(mindate) mindate, MAX(maxdate) maxdate
FROM cte1 ),
dates AS ( SELECT mindate thedate, maxdate
FROM cte2
UNION ALL
SELECT thedate + INTERVAL 1 DAY, maxdate
FROM dates
WHERE thedate < maxdate ),
shopstat AS ( SELECT DATE(create_date) thedate, COUNT(*) cnt
FROM shops
GROUP BY thedate ),
userstat AS ( SELECT DATE(create_date) thedate, COUNT(*) cnt
FROM users
GROUP BY thedate )
SELECT thedate `date`,
COALESCE(shopstat.cnt, 0) shops,
COALESCE(userstat.cnt, 0) users
FROM dates
LEFT JOIN shopstat USING (thedate)
LEFT JOIN userstat USING (thedate)
SELECT event,
CONVERT_TZ(time, '+00:00', '-04:00') AS time1
FROM events
WHERE CONVERT_TZ(time, '+00:00', '-04:00') between '2021-06-24 00:00:00' AND '2021-06-24 23:59:59'
AND lang = 'ru'
SELECT event,
CONVERT_TZ(time, '+00:00', '-04:00') AS time1
FROM events
WHERE lang = 'ru'
HAVING time1 between '2021-06-24 00:00:00' AND '2021-06-24 23:59:59'
UPDATE users u1
CROSS JOIN users u2
SET u1.amount = u1.amount - $summa,
u2.amount = u2.amount + $summa
WHERE u1.id = $client
AND u2.id = $shop;