DROP PROCEDURE IF EXISTS t1;
DELIMITER //
CREATE PROCEDURE t1 ()
BEGIN
DECLARE e, w, s, n INT;
SELECT 1 INTO e;
SELECT 2 INTO w;
SELECT 3 INTO s;
SELECT 4 INTO n;
SET e = w = s = n = NULL;
SELECT e,w,s,n;
END//
DELIMITER ;
DROP PROCEDURE IF EXISTS t2;
DELIMITER //
CREATE PROCEDURE t2 ()
BEGIN
DECLARE e, w, s, n INT;
SELECT 1 INTO e;
SELECT 2 INTO w;
SELECT 3 INTO s;
SELECT 4 INTO n;
SET e = NULL;
SET w = NULL;
SET s = NULL;
SET n = NULL;
SELECT e,w,s,n;
END//
DELIMITER ;
CALL t1();
CALL t2();
+------+------+------+------+
| e | w | s | n |
+------+------+------+------+
| NULL | 2 | 3 | 4 |
+------+------+------+------+
+------+------+------+------+
| e | w | s | n |
+------+------+------+------+
| NULL | NULL | NULL | NULL |
+------+------+------+------+select * from t;
+-------------------------------------------------------------------------+
| u |
+-------------------------------------------------------------------------+
| [{"name": "Tomas", "user_id": "52"}, {"name": "John", "user_id": "59"}] |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)
SELECT JSON_EXTRACT(u, REPLACE(REPLACE(JSON_SEARCH(u, 'one', '52'), 'user_id', 'name'), '"', '')) FROM t;
+--------------------------------------------------------------------------------------------+
| JSON_EXTRACT(u, REPLACE(REPLACE(JSON_SEARCH(u, 'one', '52'), 'user_id', 'name'), '"', '')) |
+--------------------------------------------------------------------------------------------+
| "Tomas" |
+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)ALTER IGNORE TABLE <table_name> ADD UNIQUE KEY (<name>,<name>);'db' => [
.....
'enableSchemaCache' => true,
'schemaCacheDuration' => 3600,
'schemaCache' => 'cache',
]innodb_flush_method = O_DSYNC
innodb_flush_log_at_trx_commit = 0$result = $dbh->query("SELECT id FROM test.news LIMIT 3")->fetchAll(PDO::FETCH_ASSOC);
// получили из БД список ID
// +-----+
// | id |
// +-----+
// | 100 |
// | 101 |
// | 102 |
// +-----+
$exist = [];
// делаем массив у которого ключ = ID, а значение = true
foreach ($result as $row) {
$exist[$row['id']] = true;
}
// массив новостей
$news = [
[ "id" => 102 , "titile" => "news title", "text" => "news text" ],
[ "id" => 1001, "titile" => "news title", "text" => "news text" ]
];
// проходим по каждому элементу массива с новостями
foreach ($news as $key => $item) {
// проверяем если ли в БД новость с таким ID
if (isset($exist[$item['id']]) && $exist[$item['id']] === true) {
// если такой новости нет - удаляем из массива с новостями
unset($news[$key]);
}
}INSERT INTO ..... ON DUPLICATE KEY UPDATE updated_at = NOW()CREATE DATABASE test;
use test;
CREATE TABLE t (a varchar(255), b varchar(255));
-- повесим триггер на INSERT
-- триггер будет брать значение из "...." и вставлять его в поле 'b'
CREATE TRIGGER before_insert_test_t
BEFORE INSERT ON test.t
FOR EACH ROW
SET new.b = SUBSTRING_INDEX(SUBSTRING_INDEX(new.a, '"', 2), '"', -1);
INSERT INTO t (a) VALUES('text text "QWERTY" text text');
SELECT * FROM t;
+------------------------------+--------+
| a | b |
+------------------------------+--------+
| text text "QWERTY" text text | QWERTY |
+------------------------------+--------+CREATE TABLE t
(
a varchar(255),
b varchar(255) default (SUBSTRING_INDEX(SUBSTRING_INDEX(a, '"', 2), '"', -1);)
);/var/lib/mysql/mysqld.log {
.....
postrotate
if test -x /usr/bin/mysqladmin && /usr/bin/mysqladmin ping &>/dev/null
then
/usr/bin/mysqladmin flush-logs
fi
endscript
}select User from mysql.user where User = 'Root'
Empty set (0.00 sec)
select User from mysql.user where User = 'Root' COLLATE utf8_general_ci;
+------+
| User |
+------+
| root |
+------+
select User from mysql.user where User = 'Root' COLLATE utf8_bin;
Empty set (0.00 sec)COLLATE utf8_bin;
UPDATE table_name
SET claim = CONCAT( claim, 'тут текст который надо добвить')
WHERE ...create table t (
name varchar(255),
value varchar(255)
);
insert into t values('name1', 'value1');
insert into t values('name2', 'value2');
insert into t values('name3', 'value3');
insert into t values('name4', 'value4');
insert into t values('name5', 'value5');
insert into t values('name6', 'value6');
insert into t values('name7', 'value7');
insert into t values('name8', 'value8');
insert into t values('name9', 'value9');
insert into t values('name10', 'value10');
insert into t values('name11', 'value11');
insert into t values('name12', 'value12');
insert into t values('name13', 'value13');
insert into t values('name14', 'value14');
insert into t values('name15', 'value15');
-- каждый 5-й
SELECT
num, name, value
FROM (
SELECT (@RN:=@RN+1) AS num, name, value
FROM t, (SELECT @RN:=0) AS t
) as d
WHERE num % 5 = 0;
+------+--------+---------+
| num | name | value |
+------+--------+---------+
| 5 | name5 | value5 |
| 10 | name10 | value10 |
| 15 | name15 | value15 |
+------+--------+---------+
-- каждый 3-й
SELECT
num, name, value
FROM (
SELECT (@RN:=@RN+1) AS num, name, value
FROM t, (SELECT @RN:=0) AS t
) as d
WHERE num % 3 = 0;
+------+--------+---------+
| num | name | value |
+------+--------+---------+
| 3 | name3 | value3 |
| 6 | name6 | value6 |
| 9 | name9 | value9 |
| 12 | name12 | value12 |
| 15 | name15 | value15 |
+------+--------+---------+mysqladmin passwordUPDATE mysql.user SET authentication_string = PASSWORD('*****') WHERE User = 'root' AND Host = 'localhost';