Porady

SQL/MySQL/HSQL

Poniższe porady dotyczą projektowania baz danych, języka SQL oraz systemów zarządzania bazami danych: MySQL, PostgreSQL, SQLite.

Spis treści:

[2022-11-15] Odczytanie aktualnego stanu tabel z wybranej bazy danych oraz odczytanie wartości zmiennej "AUTO_INCREMENT" dla wybranej tabeli w wybranej bazie danych

W celu odczytania aktualnego stanu tabel z wybranej bazy danych, w SZBD MySQL należy wykonać następujące zapytanie języka SQL:

SHOW TABLE STATUS; -- wyświetla aktualny status tabel w bieżącej bazie danych

SHOW TABLE STATUS FROM Nazwa_bazy_danych; -- wyświetla aktualny status tabel w bazie danych o nazwie "Nazwa_bazy_danych"

W celu odczytania wartości zmiennej "AUTO_INCREMENT" dla wybranej tabeli w wybranej bazie danych, w SZBD MySQL należy wykonać następujące zapytanie języka SQL:

SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'Nazwa_bazy_danych' AND TABLE_NAME = 'Nazwa_tabeli';

Powyższe zapytania nie zawsze wyświetlają aktualną wartość zmiennej "AUTO_INCREMENT". Poniższe polecenie zawsze wyświetla aktualną wartość tej zmiennej:

SHOW CREATE TABLE Nazwa_tabeli;

Testowane w programie:

MySQL 8.0.31-0ubuntu0.20.04.1, Ubuntu GNU/Linux: 20.04.5 LTS

Źródło wiedzy:

https://stackoverflow.com/questions/15821532/get-current-auto-increment-value-for-any-table

2022-09-28] Resetowanie zapomnianego hasła administratora (root) na serwerze w MySQL 8.0 (Ubuntu 20.04 LTS)

W tym celu

// Zatrzymujemy działanie serwera MySQL w zwykłym trybie
$ sudo service mysql stop
// Tworzymy katalog wymagany do pracy serwera MySQL i nadajemy mu odpowiednie uprawnienia
$ sudo mkdir -p /var/run/mysqld
$ sudo chown mysql:mysql /var/run/mysqld
// Uruchamiamy serwer MySQL w specjalnym trybie, w którym nie są sprawdzane prawa dostępu i nie można pracować zdalnie (można się zalogować tylko lokalnie) (powinien wyświetlić się PID uruchomionego procesu)
$ sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

// Dodatkowo możemy się upewnić, że serwer MySQL uruchomił się
$ ps aux | grep mysqld
// Uruchamiamy monitor MySQL (w tym trybie nie wymaga to podawania hasła)
$ mysql -u root

// Aktualizujemy informację o statusie sprawdzania praw dostępu w MySQL
// (pominięcie tej instrukcji może spowodować błąd podczas wykonywania instrukcji "ALTER USER")

mysql> FLUSH PRIVILEGES;

// Zmieniamy hasło użytkownika "root" (Administratora) (nie wymaga to podawania dotychczasowego hasła, które zapomnieliśmy)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'N3w_p@ssw0rD.';

mysql> quit

// Zatrzymujemy pracę serwera w trybie specjalnym

$ sudo pkill mysqld

// Uruchamiamy serwer w zwykłym trybie

$ sudo service mysql start

// Teraz do klienta MySQL możemy zalogować się za pomocą nowego hasła
$ mysql -u root -p

Testowane w programie:

MySQL 8.0.27-0, Ubuntu GNU/Linux: 20.04

Źródło wiedzy:

https://linuxconfig.org/how-to-reset-root-mysql-mariadb-password-on-ubuntu-20-04-focal-fossa-linux

[2020-01-30] Zmiana znaku zachęty (prompt) w MySQL

W tym celu w pliku /etc/mysql/conf.d/mysql.cnf należy dodać albo zmodyfikować wiersz zaczynający się od tekstu prompt=, np.

[mysql]

prompt=[\\d]\\_\\R:\\m:\\s\\_mysql>\\_

Testowane w programie:

MySQL 5.7.17-0ubuntu0.16.04.1 (Ubuntu).

Źródła wiedzy:

[2018-10-03] Zmiana wartości dla zmiennej systemowej w MySQL na przykładzie maksymalnej długości tekstu wygenerowanego funkcją "Group_Concat()"

Funkcja "Group_Concat()" łączy zawartości wielu komórek z jednej kolumny tabeli w jeden tekst.

W MySQL maksymalna długości tego tekstu wynosi domyślnie 1024 znaków.

Aby zmienić to ograniczenie w bieżącej sesji, można użyć następującego zapytania SQL:

SET SESSION group_concat_max_len = 10000; -- wartość nie mniejsza niż 4

Aby zmienić to ograniczenie do następnego restartu serwera, można użyć następującego zapytania SQL:

SET GLOBAL group_concat_max_len = 10000; -- wartość nie mniejsza niż 4

Aby zmienić to ograniczenie na stałe, w pliku "/etc/mysql/mysql.conf.d/mysqld.cnf" należy dokonać następującego wpisu :

[mysqld]

group_concat_max_len = 10000

Testowane w programie:

MySQL 5.7.23-0ubuntu0.16.04.1 (Ubuntu).

Źródła wiedzy:

[2017-12-24] Resetowanie zapomnianego hasła użytkownika root w MySQL 5.7 (Ubuntu 16.04 LTS)

W tym celu należy wykonać następujące czynności:

# Zatrzymanie serwera MySQL

sudo service mysql stop

# Ręczne utworzenie katalogu roboczego serwera MySQL

sudo mkdir /var/run/mysqld

# Nadanie własności utworzonemu wcześniej katalogowi serwerowi MySQL

sudo chown mysql:mysql /var/run/mysqld

# Ręczne uruchomienie serwera MySQL w tle, bez sprawdzania haseł użytkowników i bez obsługi sieci.

sudo mysqld_safe --skip-grant-tables --skip-networking &

# Zalogowanie użytkownika root do serwera MySQL bez podawania hasła

mysql -uroot mysql

Po uruchomieniu klienta MySQL należy:

mysql> UPDATE mysql.user SET authentication_string=PASSWORD('nowe_haslo'), plugin='mysql_native_password'

> WHERE User='root' AND Host='%';

mysql> FLUSH PRIVILEGES;

mysql> quit

Po zakończeniu połączenia z MySQL należy:

# Wyłączenie serwera MySQL

sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown

# Włączenie serwera MySQL w trybie zwykłym

sudo service mysql start

Testowane w programie:

MySQL 5.7.20-0ubuntu0.16.04.1 - (Ubuntu), Xubuntu 16.04.3 LTS.

Źródło wiedzy:

https://coderwall.com/p/j9btlg/reset-the-mysql-5-7-root-password-in-ubuntu-16-04-lts.

[2017-09-20] Odpowiednik funkcji "explode" z PHP w MySQL

Funkcja "explode" w PHP służy do wyciągania N-tego składnika z tekstu rozdzielanego separatorem. W celu zobrazowania jej działania, posłużmy się poniższym przykładem.

Oto przykładowa tabela (w tym przypadku separatorem jest ";" - nie może on występować w danych):

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

id dane

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

1 a;bc;def

2 ghij;klmno

3 pqrstuvwxyz

4

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

Mamy za zadanie wyświetlić dane jako 3 osobne kolumny: dane1, dane2, dane3.

Rozwiązanie

Można zrobić to np. za pomocą następującego polecenia SQL:

-- ********************************

-- *** PRZYGOTOWANIE DANYCH ***

-- ********************************


-- Tworzymy tymczasową tabelę "dane"

CREATE TEMPORARY TABLE przyklad

(

id BIGINT UNSIGNED PRIMARY KEY,

dane VARCHAR(18)

) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;


-- Wstawiamy powyższe dane do utworzonej właśnie tabeli

INSERT INTO przyklad VALUES

(1, "a;bc;def"),

(2, "ghij;klmno"),

(3, "pqrstuvwxyz"),

(4, "");


-- Ustawiamy parametry (dla lepszego zrozumienia kodu)

SET @separator = _utf8';' COLLATE utf8_unicode_ci; -- separator ";" z kodowaniem, jak w tabeli

SET @fragment1 = 1;

SET @fragment2 = 2;

SET @fragment3 = 3;


-- ********************************

-- *** WŁAŚCIWE ROZWIĄZANIE ***

-- ********************************


-- Wyświetlamy dane podzielone na kolumny

SELECT id,

Replace(Substring(Substring_Index(dane, @separator, @fragment1),

Length(Substring_Index(dane, @separator, @fragment1-1)) + 1),

@separator, '') AS dane1,

Replace(Substring(Substring_Index(dane, @separator, @fragment2),

Length(Substring_Index(dane, @separator, @fragment2-1)) + 1),

@separator, '') AS dane2,

Replace(Substring(Substring_Index(dane, @separator, @fragment3),

Length(Substring_Index(dane, @separator, @fragment3-1)) + 1),

@separator, '') AS dane3

FROM przyklad;

Oto analiza działania poszczególnych funkcji na przykładzie pierwszego rekordu z powyższej tabeli:

[dane1]

Substring_Index("a;bc;def", ';', 1) => "a"

Substring_Index("a;bc;def", ';', 0) => ""

Length("") + 1 => 1

Substring("a", 1) => "a"

Replace("a", ';', '' -> "a"

WYNIK: "a"


[dane2]

Substring_Index("a;bc;def", ';', 2) => "a;bc"

Substring_Index("a;bc;def", ';', 1) => "a"

Length("a") + 1 => 2

Substring("a;bc", 2) => ";bc"

Replace(";bc", ';', '' -> "bc"

WYNIK: "bc"


[dane3]

Substring_Index("a;bc;def", ';', 3) => "a;bc;def"

Substring_Index("a;bc;def", ';', 2) => "a;bc"

Length("a;bc") + 1 => 5

Substring("a;bc;def", 5) => ";def"

replace(";def", ';', '' -> "def"

WYNIK: "def"

Testowane w programie:

MySQL 5.7.19-0ubuntu0.16.04.1 (Xubuntu).

Źródła wiedzy:

[2017-09-20] Polecenie "status" w MySQL

Polecenie "status" wyświetla następujące informacje:

  1. Ogólne informacje o serwerze i sesji:

    • Connection id: ID połączenia z MySQL

    • Current database: bazę danych ustawioną jako bieżącą/aktywną/domyślną)

    • Current user: nazwę aktualnie zalogowanego uzytkownika oraz nazwę hosta z jakiego się zalogował (localhost oznacza logowanie z maszyny, na której znajduje się serwer MySQL)

    • SSL: informację o tym, czy połączenie używa SSL (czy jest szyfrowane)

    • Current pager: informuje, w jaki sposób wyświetlane są odpowiedzi serwera (stdout oznacza wysyłanie odpowiedzi na ekran)

    • Using outfile: informuje, do jakiego pliku zostają wysyłane odpowiedzi serwera

    • Using delimiter: informuje jaki znak lub ciąg znaków oznacza kończy zapytanie lub rozdziela zapytania

    • Server version: wersja serwera MySQL

    • Protocol version: wersja protokołu komunikacyjnego z serwerem

    • Connection: rodzaj połączenia z serwerem

    • Server characterset: kodowanie używane przez serwer

    • Db characterset: kodowanie używane przez bazę danych

    • Client characterset: kodowanie używane przez klienta (np. Monitor MySQL)

    • Connection characterset: kodowanie używane w bieżącym połączeniu z serwerem (bieżącej sesji)

    • UNIX socket: ścieżka dostępu do pliku gniazda

    • Uptime: czas pracy serwera

  2. Statystyki obciążenia serwera:

    • Threads: liczba połączeń z serwerem (pojedynczy użytkownik może mieć wiele połączeń)

    • Questions: liczba pytań do serwera (jedno zapytanie może składać się z wielu pytań)

    • Slow queries: liczba powolnych zapytań (takich, których czas wykonania przekroczył określony czas, domyślnie 10 sekund)

    • Opens: liczba otwieranych tabel

    • Flush tables: liczba operacji czyszczenia wewnętrznej pamięci podręcznej (cache) silnika tabel

    • Open tables: liczba aktualnie otwartych tabel

    • Queries per second avg (Questions / Uptime): średnia liczba zapytań na sekundę

Testowane w programie:

MySQL 5.7.19-0ubuntu0.16.04.1 (Xubuntu).

Źródła wiedzy: