Szybkie posty

Jeżeli coś jest wartego uwagi a nie jest to materiał na pełnoprawnwgo posta na blogu to będę starać się to umieszczać w tym miejscu.

#ajax #apache #behat #bitbacket #bootstrap #composer #cookies #cqrs #css #css flex #ct8 #curl #docker #doctrine #edukacja #enet #enp sla #filmy #firma #funkcje php #git #google #htaccess #html #inne #javascript #jedzenie #jquery #js/jquery #kawały #krypto #laravel #linux #oop #pdo #php #php wzorce narzędzia #phpmyadmin #phpspec #phpstan #phpstorm #phpunit #podcast #rabbit #redis #seo #soap #sql #symfony #szukanie po stringach w php #twig #virtual host #visual studio code #vue #wamp #windows #wino-nalewki #wyrazenia regularne #wzorce projektowe #xml #xxx #zdjecia #złote myśli
  • Szukasz po tagu: sql
  • Ilość rekordów w bazie: 43

Dodanie znaków do selecta

'CONCAT(LPAD(pwh.open_from_hour, 2, 0),\':\',LPAD(pwh.open_from_minute, 2, 0)) as open_hour_from',
'CONCAT(LPAD(pwh.open_to_hour, 2, 0),\':\',LPAD(pwh.open_to_minute, 2, 0)) as open_hour_to',

Jeżel:

open_from_hour -> 0

open_from_minute ->

to powstanie

open_from_hour -> 00:00

Grupowanie z sortowaniem

SELECT * FROM 
  `alert_order_history_temporary` aoh 
WHERE 
  aoh.id IN (
    SELECT 
      max_id 
    FROM 
      (
        select 
          concat(aoh2.alert_id, aoh2.action_type) as group_by, 
          max(aoh2.id) as max_id 
        from 
          `alert_order_history_temporary` aoh2 
        WHERE 
          aoh2.order_id = 113904894 
          AND aoh2.alert_id = 85 
          AND aoh2.action_type IN ('ATTACHED', 'DETACHED') 
        group by 
          group_by 
        order by 
          max_id ASC
      ) AS sub
  )

zapytanie modyfikacja

SELECT 
  DISTINCT (`os`.`inpost_machine`)
FROM 
  `order` `o` 
  INNER JOIN `order_shipment` `os` ON `os`.`order_id` = `o`.`id` 
  INNER JOIN `transport` `t` ON `t`.`id` = `o`.`transport_id` 
  INNER JOIN `transport_type` `tt` ON `tt`.`id` = `t`.`type_id` 
WHERE 
  (`o`.`deleted_at` IS NULL) 
  AND (`o`.`website_id` = 5) 
  AND (`o`.`customer_id` = '10604984') 
  AND (`tt`.`code` = 'paczkomat') 
ORDER BY 
  `o`.`created_at` DESC 
LIMIT 
  1
SELECT 
  `inpost_machine` 
FROM 
  (
    SELECT 
      `os`.`inpost_machine`, 
      MAX(o.created_at) 
    FROM 
      `order` `o` 
      INNER JOIN `order_shipment` `os` ON `os`.`order_id` = `o`.`id` 
      INNER JOIN `transport` `t` ON `t`.`id` = `o`.`transport_id` 
      INNER JOIN `transport_type` `tt` ON `tt`.`id` = `t`.`type_id` 
    WHERE 
      (`o`.`deleted_at` IS NULL) 
      AND (`o`.`website_id` = 5) 
      AND (`o`.`customer_id` = '10604984') 
      AND (`tt`.`code` = 'paczkomat') 
    GROUP BY 
      `os`.`inpost_machine` 
    ORDER BY 
      MAX(o.created_at) DESC
  ) AS sub LIMIT 5

Zduplikowanie wartosći w bazie/tabeli

SELECT code, COUNT(*) c FROM `pos` GROUP BY code HAVING c > 1;

zapytanie między dwiema datami

WHERE o.created_at BETWEEN '2023-04-01' AND '2023-05-18'

kolejnosć kolum w tabeli

https://stackoverflow.com/questions/894522/is-there-any-reason-to-worry-about-the-column-order-in-a-table

sprawdzenie pustych powiązań

SELECT * from order_has_status ohs
LEFT JOIN order_status_history osh 
ON osh.status_id = ohs.status_id
WHERE osh.status_id IS NULL 

zapytanie po dacie

select COUNT(*), MONTH(created_at) FROM `order` WHERE 
created_at >= '2021-01-01 00:00:00'
AND created_at <= '2021-11-01 00:00:00'
GROUP BY YEAR(created_at), MONTH(created_at)
select COUNT(*) FROM `order` WHERE created_at >= '2021-01-01 00:00:00' AND created_at <= '2021-11-01 00:00:00'

dodanie spacji w mysqlu

UPDATE `enp0031_prod`.`postcode`
SET value = INSERT(value, 4, 0, ' ') 
WHERE TYPE = 'greece'
and length(value)=5

//12345
//na 123 45

zaytanie po dacie

Przed

SELECT
    MONTH(o.created_at) AS month,
    COUNT(1) AS 'total in month',
    ((SELECT COUNT(1) FROM `order` AS o1 FORCE INDEX (idx_created_at) WHERE o1.customer_id IS NOT NULL AND MONTH(o1.created_at) = MONTH(o.created_at) AND YEAR(o1.created_at) = 2020) / COUNT(1)) * 100 AS '% logged in',
    ((SELECT COUNT(1) FROM `order` AS o1 FORCE INDEX (idx_created_at) WHERE o1.customer_id IS NULL AND MONTH(o1.created_at) = MONTH(o.created_at) AND YEAR(o1.created_at) = 2020) / COUNT(1)) * 100 AS '% guests'
FROM `order` AS o
FORCE INDEX (idx_created_at)
WHERE YEAR(o.created_at) = 2020
GROUP BY MONTH(o.created_at);

Po

SELECT
    MONTH(o.created_at) AS month,
    COUNT(1) AS 'total in month',
    ((SELECT COUNT(1) FROM `order` AS o1 FORCE INDEX (idx_created_at) WHERE o1.customer_id IS NOT NULL AND MONTH(o1.created_at) = MONTH(o.created_at) AND YEAR(o1.created_at) = 2020) / COUNT(1)) * 100 AS '% logged in',
    ((SELECT COUNT(1) FROM `order` AS o1 FORCE INDEX (idx_created_at) WHERE o1.customer_id IS NULL AND MONTH(o1.created_at) = MONTH(o.created_at) AND YEAR(o1.created_at) = 2020) / COUNT(1)) * 100 AS '% guests'
FROM `order` AS o
FORCE INDEX (idx_created_at)
WHERE YEAR(o.created_at) = 2020
GROUP BY MONTH(o.created_at);

 


sprawdzenie czy istnieje id w innej tabeli

SELECT o.id FROM  `order` o 
WHERE o.payment_id = 112
AND NOT EXISTS (SELECT 1 FROM payment_santander_status WHERE payment_santander_status.order_id = o.id)
limit 1000

import/export mysql

//export - backup tabeli

sudo mysqldump -u root -p nazwa_bazy nazwa_tabeli > plik.sql
./mysqldump.exe -u root -p m4856_cypherlarva auctions > auctions_gt.sql //windows wamp

//import 

sudo mysql -u root -p m4856_cypherlarva < m4856_cypherlarva.sql
//docker!!!
docker exec -i mysql mysql -uroot -prootPASS auctions < auctions.sql
./mysql.exe -u root  m4856_cypherlarva < auctions.sql //windows

jak padnie inndb wywalić

ib_logfile1
ib_logfile0

migracja z szukaniem tabel o podobnych nazwach

<?php

declare(strict_types=1);

namespace Application\Migrations;

use Doctrine\DBAL\Schema\Schema;
use Enp\Bundle\CoreBundle\Migration\AbstractEnpMigration;
use PDO;

final class Version20200819084526 extends AbstractEnpMigration
{
    public function up(Schema $schema): void
    {
        foreach ($this->getOrderExtensionTables() as $tableName) {
            if (empty($tableName)) {
                continue;
            }

            $this->addSql(sprintf('ALTER TABLE %s ADD payment_document_number VARCHAR(255) DEFAULT NULL', $tableName));
        }
    }

    public function down(Schema $schema): void
    {
        foreach ($this->getOrderExtensionTables() as $tableName) {
            if (empty($tableName)) {
                continue;
            }
            $this->addSql(sprintf('ALTER TABLE %s DROP payment_document_number', $tableName));
        }
    }

    private function getOrderExtensionTables(): array
    {
        $tables = $this->connection->executeQuery(
            "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME LIKE '%\_order_extension';"
        )->fetchAll(PDO::FETCH_COLUMN);
        $tables[] = 'order_extension';

        return $tables;
    }
}

mysql group by z max wartością kolumny

  public function findLatestCreationDatesByOrderAndStatusesIds(
        OrderInterface $order,
        array $statusesIds
    ): array {
        $queryBuilder = $this->getQueryBuilder()
            ->select(
                \sprintf('IDENTITY(%s.status) as status', $this->getAlias()),
                \sprintf('max(%s.createdAt) as createdAt', $this->getAlias())
            )
            ->andWhere(\sprintf('%s.order = :order', $this->getAlias()))
            ->setParameter(
                'order',
                $order->getId(),
                PDO::PARAM_INT
            )
            ->andWhere(sprintf('%s.status IN (:statuses)', $this->getAlias()))
            ->setParameter(
                'statuses',
                $statusesIds,
                Connection::PARAM_INT_ARRAY
            );

        $queryBuilder->groupBy(sprintf('%s.status', $this->getAlias()));

        return $queryBuilder->getQuery()->getResult();
    }

cfg zmienne opis

[mysqld]
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000

Co to jest?

  • innodb_read_io_threads - Liczba wątków We / Wy dla operacji odczytu w InnoDB.
  • innodb_write_io_threads - Liczba wątków we / wy dla operacji zapisu w InnoDB.
  • innodb_io_capacity - Górny limit aktywności we / wy wykonywanej przez zadania w tle InnoDB, takie jak opróżnianie stron z puli buforów i scalanie danych z bufora wstawiania.
 

Pokazywanie wartości zmiennych

show variables like '%slow%';

mysql zmiana timeout

link

Please note that since 10.1.10, MariaDB uses systemd to start the service. The /etc/init.d/mysql script is no longer used, so MYSQLD_STARTUP_TIMEOUT has no effect.

You need to find your mariadb.service file. In our case, it did not contain a timeout so the MariaDB default was being used. Just add /etc/systemd/system/mariadb.service.d/override.conf file, and put in it:

[Service]
TimeoutStartSec = 0
In the [Service] section, and it will never time out. After create the file:

# systemctl daemon-reload
# systemctl restart mysql.service

Reinstalacja mysql na linuxie

sudo apt-get remove --purge mysql*
sudo apt-get purge mysql* 
sudo apt-get autoremove 
sudo apt-get autoclean
sudo apt-get remove dbconfig-mysql
sudo apt-get dist-upgrade

//instalcja mysql sudo apt-get install mysql-server
//instalacja mariadb sudo apt-get install mariadb-server

Usawienie hasła dla usera w mysql

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root');

Izolacja kolumny w mysql

Jeżeli w zapytaniu nie zostały wyizolowanie zindeksowane kolumny, wówczas baza danych MySQL w zasadzie nie może używać indeksów na kolumnach, przynajmniej do chwili ich
wyizolowania w zapytaniu. Pojęcie „izolacji” kolumny oznacza, że nie powinna być częścią wyrażenia, ani znajdować się wewnątrz funkcji używanej w zapytaniu. Przykładowo poniższe zapytanie nie będzie używało indeksu na kolumnie actor_id:
mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
Człowiek może z łatwością dostrzec, że klauzula WHERE odpowiada wyrażeniu actor_id = 4, ale baza danych MySQL nie będzie mogła rozwiązać równania dla kolumny actor_id. To jest zadanie dla człowieka. Należy nabrać nawyku upraszczania kryteriów klauzuli WHERE, tak aby zindeksowana kolumna była jedyną znajdującą się po jej stronie operatora porównania.Poniżej przedstawiono inny przykład często popełnianego błędu:
mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;
Powyższe zapytanie odszuka wszystkie rekordy, dla których wartość date_col będzie mniejsza niż dziesięć dni, ale nie będzie używać indeksów z powodu zastosowania funkcji TO_DAYS().Lepszym sposobem zapisania tego zapytania może być:
mysql> SELECT ... WHERE date_col >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
Powyższe zapytanie nie będzie miało problemów z wykorzystaniem indeksów, ale wciąż można je usprawnić na pewien sposób. Odwołanie do CURRENT_DATE uniemożliwia buforowi zapytania buforowanie jego wyników. Rozwiązaniem jest zastąpienie odwołania do CURRENT_DATE dosłowną wartością daty:
mysql> SELECT ... WHERE date_col >= DATE_SUB('2008-01-17', INTERVAL 10 DAY);


Uuid w mysql

Jeżeli przechowywane są wartości UUID, należy usunąć myślniki lub najlepiej skonwertować za pomocą funkcji UNHEX() wartości UUID na 16-bajtowe liczby, które następnie będą przechowywane w kolumnie BINARY(16). Pobranie wartości w formacie szesnastkowym odbywa się za pomocą funkcji HEX().
Wartości wygenerowane przez UUID() mają charakterystykę inną od wartości wygenerowanych przez funkcję kryptograficzną, np. SHA1(); wartości UUID są nierównomiernie rozproszone i w pewien sposób sekwencyjne. Jednak nadal nie jest to tak dobre rozwiązanie jak monotonie zwiększana o jednostkę liczba całkowita.


fulltext mysql

SELECT uniqueIDS FROM
(
    SELECT * FROM search_V2 WHERE siteID=1 AND status=1
) A
WHERE MATCH(data) AGAINST ('scale' IN BOOLEAN MODE);

link

 


mysql slow logi

W tym miejscu można przytoczyć następujący przykład: powtarzane tysiąc razy na sekundę zapytanie wykonywane w ciągu dziesięciu milisekund stanowi większe
obciążenie dla serwera niż zapytanie wykonywane raz na sekundę i trwające dziesięć sekund.

Przedstawione poniżej opcje konfiguracyjne powodują włączenie dziennika zdarzeń, przechwycenie wszystkich zapytań wykonywanych dłużej niż dwie sekundy oraz zarejestrowanie zapytań, które nie używają indeksów. Dziennik będzie również zapisywał powolne polecenia
administracyjne, takie jak OPTIMIZE TABLE:

log-slow-queries = <nazwa_pliku>
long_query_time = 2
log-queries-not-using-indexes
log-slow-admin-statements

Profilowanie | 83
Powyższe opcje konfiguracyjne należy dostosować do własnych potrzeb, a następnie umieścić w pliku konfiguracyjnym serwera o nazwie my.cnf. 

Wartość domyślna opcji long_query_time wynosi dziesięć sekund. W większości zastosowań to zbyt długi okres czasu, autorzy zazwyczaj ustawiają tutaj dwie sekundy. Jednak w określonych sytuacjach nawet jedna sekunda będzie zbyt długim okresem czasu. Dokładne dostrojenie procesu rejestrowania zdarzeń zostało przedstawione w kolejnym podrozdziale.

W MySQL 5.1 zmienne systemowe slow_query_log i slow_query_log_file zapewniają w trakcie działania kontrolę nad dziennikiem powolnych zdarzeń, ale w MySQL 5.0 nie można
włączyć lub wyłączyć powolnych zdarzeń bez ponownego uruchomienia serwera MySQL. W MySQL 5.0 obejściem problemu zwykle jest zmienna long_query_time, której wartość
można dynamicznie zmieniać. Przedstawione poniżej polecenie faktycznie nie wyłącza dziennika powolnych zdarzeń, ale praktycznie daje taki efekt. (Jeżeli jakiekolwiek zapytanie w aplikacji jest wykonywane w czasie dłuższym niż 10000 sekund, to i tak należy je zoptymalizować!).


sql pokazywanie statusu cache

SHOW VARIABLES LIKE '%query_cache_type%'

złe praktyki w sql

link

paginacja dlaczego ejst wolna


największa ilość wpisów z danej kategori

SELECT cat_id,COUNT(cat_id) FROM auctionsEngineTest GROUP BY cat_id ORDER BY COUNT(cat_id) DESC LIMIT 1

Mysql najlepsze kodowanie znaków

It is best to use character set utf8mb4 with the collation utf8mb4_unicode_ci.


Zapytanie zliczające w relacyjnej bazie danych

Mamy tabelę 'users':
------------------------------
id, name, surname
------------------------------

i tabelę 'trade' gdzie wystawiają uzytkownicy przedmioty
--------------------------------------------------
id, item, id_user (relacja do users.id)
--------------------------------------------------

Jak wyświetlić użytkowników którzy mają wystawione co najmniej powiedzmy 5 przedmiotów w tabeli trade?
SELECT users.name FROM users 
JOIN trade ON trade.id_user = users.id
GROUP BY users.id
HAVING COUNT(trade.id) >= 5

Update pola w tabeli z jsona z tej samej tabeli w zakresie czasowym

UPDATE multi_step_form_client_data SET pesel = REPLACE(json_extract(serializedData,'$.pesel'),'"','') where createdAt >= (now() - interval 30 day)

Wyszukiwanie po jsonie

SELECT * FROM `multi_step_form_client_data` WHERE JSON_CONTAINS(serializedData, '{"pesel" : "86042287797"}')

Wyszukiwanie wielu pól w relacji many_to_many

SELECT c0_.id AS id_0, c0_.created AS created_1, c0_.affiliate_network AS affiliate_network_2, c0_.affiliate_account AS affiliate_account_3, c0_.description AS description_4, c0_.full_content AS full_content_5, c0_.tag_product AS tag_product_6 FROM campaign c0_ 
LEFT JOIN campaign_taggeneral c2_ ON c0_.id = c2_.campaign_id 
LEFT JOIN tag_general t1_ ON t1_.id = c2_.tag_general_id
WHERE t1_.id IN (?) 
AND c0_.created >= ? AND c0_.created <= ? ORDER BY c0_.created ASC

//symfony
$query->leftJoin('i.tagsGeneral', 'j')
->where('j.id in (:subCompanyId)')->setParameter("subCompanyId", $filterData->getFilterTagsGeneral());

tuning mysqla

/etc/mysql/my.cnf

link

Koniecznie pod tagiem [mysqld] !!!!

 

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

!includedir /etc/mysql/conf.d/


[mysqld]
max_connections = 500

max_allowed_packet=768M
key_buffer_size=2048M //dla mysam !!! malo wazne

innodb_buffer_pool_size=512M
innodb_log_file_size=128M
innodb_log_buffer_size=512M

query_cache_limit		= 0M
query_cache_size		= 0M

innodb_buffer_pool_instances=2

#tmp_table_size=2G
#max_heap_table_size=2G

Md5 wyszukiwanie w mysql po dwóch kolumnach

link

 

Przydatne info mysql

https://x-coding.pl/blog/developers/mysql-explain-uzywac-czytac/

Partycjonowanie

CREATE TABLE `salaries_2` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL
) 

PARTITION BY RANGE(YEAR(from_date)) (
    PARTITION p0 VALUES LESS THAN (1985),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (2000),
    PARTITION p3 VALUES LESS THAN (2002),
    PARTITION p4 VALUES LESS THAN MAXVALUE
)

explain select count(*) from salaries_2 where from_date = '2001-01-01';

CREATE TABLE `salaries_3` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL
) 

PARTITION BY HASH(emp_no)
PARTITIONS 6;

Pokazywanie wartosci danej zmiennej w mysql

mysql -u root -p
SHOW VARIABLES LIKE "max_connections";

Restart mysql na linuxie

sudo /etc/init.d/mysql restart

Długość największego pola

SELECT MAX(LENGTH(field_to_query)) FROM table_to_query;

Kopiowanie tabeli

Kopiowanie całej tabeli

INSERT INTO newTable
SELECT * FROM oldTable

Kopiowanie wybranych wierszy

INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable

Funkcje agregujące

Funkcja agregująca:

Funkcja działająca w zbiorze wierszy w celu obliczenia i zwrócenia tylko
jednej wartości.

Funkcja Opis
AVG() zwraca średnią wartość kolumny
COUNT() zwraca liczbę wierszy w kolumnie
MAX() zwraca największą wartość w kolumnie
MIN() zwraca najmniejszą wartość w kolumnie
SUM() zwraca sumę wartości w kolumnie

 


Funkcje usuwające niepotrzebne spacje

Funkcje TRIM

Wiele baz danych obsługuje trzy funkcje odcinające niepotrzebne spacje. Funkcja
RTRIM() usuwa spacje z prawej strony tekstu, funkcja LTRIM() usuwa je
z lewej strony, natomiast funkcja TRIM() usuwa spacje z obu stron tekstu.Przykład bez użycia:

SELECT Concat(dost_nazwa, ' (', dost_kraj, ')')
FROM Dostawcy
ORDER BY dost_nazwa;

Fun and Games         (Anglia               )
Furball Inc.                   (USA                   )
Jouets et ours            (Francja             )
Lalki S.A.                       (Polska               )
Misie Pysie                  (Polska               )
Misiowe Imperium (Polska               )

Po użyciu funkcji RTRIM(): 

SELECT RTRIM(dost_nazwa) + ' (' + RTRIM(dost_kraj) + ')'
FROM Dostawcy
ORDER BY dost_nazwa;

Fun and Games (Anglia)
Furball Inc. (USA)
Jouets et ours (Francja)
Lalki S.A. (Polska)
Misie Pysie (Polska)
Misiowe Imperium (Polska)


Reset tabeli z usunięciem auto incrementowanych wartości

TRUNCATE table nazwatabeli