« MariaDB » : différence entre les versions
De Banane Atomic
Aller à la navigationAller à la recherche
(→Alter) |
|||
(56 versions intermédiaires par le même utilisateur non affichées) | |||
Ligne 1 : | Ligne 1 : | ||
[[Category:SQL]] | [[Category:SQL]] | ||
[[Category:Linux]] | [[Category:Linux]] | ||
= Links = | |||
* [https://mariadb.org/mariadb/all-releases/ All releases] | |||
* [https://mariadb.com/kb/en/release-notes Release notes] | |||
= Serveur = | = Serveur = | ||
<kode lang="bash"> | <kode lang="bash"> | ||
Ligne 10 : | Ligne 14 : | ||
</kode> | </kode> | ||
== [https://mariadb.com/kb/en/ | == [https://mariadb.com/kb/en/mariadb-dump/ Dump] == | ||
<kode lang='bash'> | <kode lang='bash'> | ||
sudo mariadb-dump -x -e -B db1 -r /var/mariadb/dump/db1.sql | |||
# - | # -x / --lock-tables lock all of the tables before performing the backup | ||
# --all-databases toutes les bases de données (même les bases système) | # -e bundle INSERT statements together for each table | ||
# > | # -A / --all-databases toutes les bases de données (même les bases système) | ||
# -B / --databases db1 db2 dump the db1 and db2 only | |||
# -r file path | |||
# > file path | |||
# -u user | |||
# -p prompt password / -p'password' | |||
# store the password into this variable, so you don't have to pass it to the command line | # store the password into this variable, so you don't have to pass it to the command line | ||
Ligne 29 : | Ligne 38 : | ||
password=mypwd | password=mypwd | ||
</filebox> | </filebox> | ||
* [https://mariadb.com/kb/en/making-backups-with-mariadb-dump/ Making Backups with mariadb-dump] | |||
== Import == | == Import == | ||
Ligne 84 : | Ligne 95 : | ||
show databases; | show databases; | ||
# | # create db1 | ||
create database | create database 'db1' character set 'utf8mb4' collate 'utf8mb4_general_ci'; | ||
# | # create db1 if it doesn't exist yet | ||
create database if not exists | create database if not exists db1; | ||
# supprime ma_base_de_donnees | # supprime ma_base_de_donnees | ||
Ligne 100 : | Ligne 111 : | ||
=== [https://dev.mysql.com/doc/refman/8.0/en/charset-database.html Encoding] === | === [https://dev.mysql.com/doc/refman/8.0/en/charset-database.html Encoding] === | ||
<kode lang=' | <kode lang='mariadb'> | ||
-- migrer la db vers un encoding utf8 | -- migrer la db vers un encoding utf8 | ||
ALTER DATABASE | ALTER DATABASE `db1` | ||
CHARACTER SET utf8mb4 | |||
COLLATE utf8mb4_unicode_ci; | |||
-- afficher l'encoding et la collation d'une bdd | -- afficher l'encoding et la collation d'une bdd | ||
Ligne 132 : | Ligne 145 : | ||
name varchar(10) not null unique, | name varchar(10) not null unique, | ||
groupId tinyint unsigned not null references group (Id), --FK | groupId tinyint unsigned not null references group (Id), --FK | ||
computedColumn int as (GroupId * 5) | computedColumn int as (GroupId * 5) persistent, | ||
index ux_user_name (name), -- UX | index ux_user_name (name), -- UX | ||
Ligne 142 : | Ligne 155 : | ||
constraint uc_user_name UNIQUE (name), -- another way to create a name UC | constraint uc_user_name UNIQUE (name), -- another way to create a name UC | ||
primary key (Id) | primary key (Id) -- another way to create a PK | ||
character set 'utf8mb4' collate 'utf8mb4_general_ci'; | |||
</kode> | </kode> | ||
Ligne 149 : | Ligne 163 : | ||
* [https://dev.mysql.com/doc/refman/8.0/en/create-index.html index] | * [https://dev.mysql.com/doc/refman/8.0/en/create-index.html index] | ||
* [https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html foreign key] | * [https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html foreign key] | ||
* [https:// | * [https://mariadb.com/kb/en/generated-columns generated column] | ||
* [http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html Storage engines] | * [http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html Storage engines] | ||
Ligne 175 : | Ligne 189 : | ||
=== Alter === | === Alter === | ||
<kode lang= | <kode lang=mariadb> | ||
# | # add a new column with a foreign key | ||
alter table `ma_table` | alter table `ma_table` | ||
add ` | add `new_col` varchar(32) not null default 'default value' after `my_col`, | ||
add foreign key (`new_col`) | |||
references `other_table` (`other_col`); | |||
# modify my_column to be datetime not null | # modify my_column to be datetime not null | ||
alter table `my_table` | alter table `my_table` | ||
modify `my_column` datetime not null; | modify `my_column` datetime not null; | ||
# rename the table | |||
rename table `old_name` | |||
to `new_name`; | |||
# rename column | |||
alter table `my_table` | |||
rename column `current_name` to `new_name`; | |||
# supprimer « ma_colonne » dans « ma_table » | # supprimer « ma_colonne » dans « ma_table » | ||
alter table `ma_table` | |||
drop `ma_colonne`; | |||
# add / remove a unique constraint | |||
alter table `my_table` | |||
add constraint `uc_my_table_my_column` unique (`my_column`); | |||
alter table `my_table` | |||
drop constraint `uc_my_table_my_column`; | |||
# foreign key | |||
alter table `my_table` | |||
add constraint `fk_my_table_other_table_my_column` | |||
foreign key (`my_column`) | |||
references `other_table` (`other_column`); | |||
</kode> | </kode> | ||
Ligne 268 : | Ligne 305 : | ||
You should drop all the account with empty user names.}} | You should drop all the account with empty user names.}} | ||
= [https:// | = [https://mariadb.com/kb/en/data-types Data types] = | ||
{| class="wikitable wtp" | {| class="wikitable wtp" | ||
! Type | ! Type | ||
! Bytes | ! Bytes | ||
Ligne 285 : | Ligne 322 : | ||
| BIGINT || 8 || -9'223'372'036'854'775'808 || 9'223'372'036'854'775'807 | | BIGINT || 8 || -9'223'372'036'854'775'808 || 9'223'372'036'854'775'807 | ||
|} | |} | ||
{| class="wikitable wtp" | |||
|+ [https://mariadb.com/kb/en/string-data-types String data types] | |||
! Type | |||
! Max Length | |||
! Description | |||
|- | |||
| [https://mariadb.com/kb/en/varchar VARCHAR] || 65'532 || A variable-length string. The range of the max lenght is 0 to 65'532 or 64KB.<br> utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21'844 characters. | |||
|- | |||
| [https://mariadb.com/kb/en/char CHAR] || 255 || A fixed-length string, right-padded with spaces to the specified length. The range of the lenght is 0 to 255. | |||
|- | |||
| [https://mariadb.com/kb/en/longtext LONGTEXT] || 4GB || A TEXT column with a maximum length of 4,294,967,295 or 4GB characters. | |||
|} | |||
{| class="wikitable wtp" | {| class="wikitable wtp" | ||
! Data Type | ! Data Type | ||
Ligne 350 : | Ligne 401 : | ||
-- changer le charset et la collation d'une bdd | -- changer le charset et la collation d'une bdd | ||
ALTER DATABASE <db_name> CHARACTER SET utf8mb4 COLLATE | ALTER DATABASE <db_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; | ||
-- changer le charset et la collation d'une table | -- changer le charset et la collation d'une table | ||
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE | ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; | ||
-- changer le charset et la collation d'une colonne | -- changer le charset et la collation d'une colonne | ||
ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE | ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; | ||
</kode> | </kode> | ||
Ligne 375 : | Ligne 426 : | ||
) as table3_id | ) as table3_id | ||
from table2; | from table2; | ||
-- insert if not exists | |||
insert into table1 (column1, column2) | |||
select 'value1', 'value2' | |||
where not exists | |||
( | |||
select * from table1 | |||
where column1 = 'value1' | |||
); | |||
-- set the last inserted id in the @id variable | -- set the last inserted id in the @id variable | ||
Ligne 381 : | Ligne 441 : | ||
== [https://dev.mysql.com/doc/refman/8.0/en/update.html Update] == | == [https://dev.mysql.com/doc/refman/8.0/en/update.html Update] == | ||
<kode lang=' | <kode lang='mariadb'> | ||
update my_table | update my_table | ||
set column1 = 10, | set column1 = 10, | ||
Ligne 387 : | Ligne 447 : | ||
where column3 = 0; | where column3 = 0; | ||
update | -- update some rows only with values from another table and filter with a query on another table | ||
(select | update table1 as dest, | ||
from | ( | ||
set dest. | select column1, column2 | ||
where dest. | from table2 | ||
) as src | |||
set dest.column1 = src.column1 | |||
where dest.column2 = src.column2; | |||
-- update some rows only, filter with a join to another table | |||
update table1 as t1 | |||
join table2 as t2 | |||
on t2.t1_id = t1.id | |||
set t1.column1 = 'value1' | |||
where t2.column1 = 'value2'; | |||
</kode> | </kode> | ||
Ligne 468 : | Ligne 538 : | ||
{{info | WHERE is applied before GROUP BY, HAVING is applied after.}} | {{info | WHERE is applied before GROUP BY, HAVING is applied after.}} | ||
= [https://mariadb.com/kb/en/with Common Table Expressions CTE] = | |||
<kode lang='mariadb'> | |||
WITH cte AS | |||
( | |||
SELECT a | |||
FROM table1 | |||
WHERE b >= 'c' | |||
) | |||
SELECT * | |||
FROM table2, cte | |||
WHERE table2.c = cte.a; | |||
</kode> | |||
= [https://www.mysqltutorial.org/mysql-temporary-table Temp table] = | = [https://www.mysqltutorial.org/mysql-temporary-table Temp table] = | ||
Temporary tables are session specific, they are dropped automatically when the connection is closed. | Temporary tables are session specific, they are dropped automatically when the connection is closed. | ||
<kode lang=' | <kode lang='mariadb'> | ||
DROP TEMPORARY TABLE IF EXISTS temp1; | DROP TEMPORARY TABLE IF EXISTS temp1; | ||
Ligne 711 : | Ligne 794 : | ||
ORDER BY table_name; | ORDER BY table_name; | ||
</kode> | </kode> | ||
= Windows = | |||
* {{boxx|C:\Program Files\MariaDB 11.3\data\my.ini}} | |||
== [https://mariadb.com/kb/en/authentication-plugin-named-pipe/ Named pipe authentication] == | |||
Allow a connection with the current Windows user name. Equivalent to the UNIX SOCKET. | |||
<kode lang='mariadb'> | |||
# install the plugin | |||
INSTALL SONAME 'auth_named_pipe'; | |||
# check if named pipe authentication is enabled | |||
SHOW GLOBAL VARIABLES LIKE 'named_pipe'; | |||
# create a new user which will identified himself with the named pipe | |||
CREATE USER [WindowsUser]@localhost IDENTIFIED VIA named_pipe; | |||
</kode> | |||
<filebox fn='C:\Program Files\MariaDB 11.3\data\my.ini'> | |||
[mysqld] | |||
# enable name pipe with pipe named MARIADB | |||
enable-named-pipe | |||
socket=MySQL | |||
</filebox> | |||
<kode lang='ps'> | |||
# connection to the database | |||
mariadb --protocol=PIPE | |||
# the current Windows user is used and the default pipe name MySQL | |||
</kode> | |||
<kode lang='dos'> | |||
REM get the current user | |||
echo %USERNAME% | |||
</kode> | |||
{{info | Connection String: {{boxx|1="server=localhost;database=MyDb;user=WindowsUser;protocol=pipe"}}}} | |||
= Features by version = | = Features by version = | ||
Ligne 717 : | Ligne 836 : | ||
! Features | ! Features | ||
|- | |- | ||
| [https://mariadb.com/kb/en/changes-improvements-in-mariadb-106 | | [https://mariadb.com/kb/en/changes-improvements-in-mariadb-106 10.6] || | ||
* Implement SQL-standard SELECT ... OFFSET ... FETCH | * Implement SQL-standard SELECT ... OFFSET ... FETCH | ||
|- | |||
| [https://mariadb.com/kb/en/changes-improvements-in-mariadb-10-2 10.2] || | |||
* [https://mariadb.com/kb/en/with CTE] (10.2.1) | |||
|} | |} |
Dernière version du 9 novembre 2024 à 14:56
Links
Serveur
# Connection with unix_socket authentication sudo mysql # Connection with user and password mysql -u root -p |
Dump
sudo mariadb-dump -x -e -B db1 -r /var/mariadb/dump/db1.sql # -x / --lock-tables lock all of the tables before performing the backup # -e bundle INSERT statements together for each table # -A / --all-databases toutes les bases de données (même les bases système) # -B / --databases db1 db2 dump the db1 and db2 only # -r file path # > file path # -u user # -p prompt password / -p'password' # store the password into this variable, so you don't have to pass it to the command line export MYSQL_PWD=mypwd |
Si le mot de passe contient un des caractères suivant * ? [ < > & ; ! | $ ( ) il faut l'encadrer de quotes. |
~/.my.cnf |
# set username and password here, so you don't have to pass them to the command line [mysqldump] user=myuser password=mypwd |
Import
mysql -u utilisateur -pmot_de_passe -D base_de_données --verbose < fichier.sql > fichier.log # -D définit la bdd dans laquelle sera exécuté le fichier sql # --one-database définit l'unique bdd à importer depuis un fichier sql qui contient le backup de plusieurs bdd sudo mysql << EOF use 'MyDb'; truncate MyTable; source MyFile.sql; EOF |
# déjà connecté use 'base-de-données'; source fichier.sql; |
Executer une commande
mysql -h [ip] -D [database name] -u [user] -p[password] -e "commande mysql" -vvv # -vvv pour afficher la sortie mysql dans le terminal mysql -u [user] -p[password] << EOF commandes mysql EOF # il faut mettre le mot de passe entre simple quote s'il contient les caractères suivants : * ? [ < > & ; ! | $ ( ) mysql -u [user] -p'[password]' |
Executer un fichier
cd /ou/se/trouve/mon/fichier mysql -u utilisateur -pmot_de_passe -D base_de_données < mon_fichier.sql |
# si on est déjà connecté au serveur MySql use 'base-de-données'; source mon_fichier.sql |
Create database and tables
Database
# 3 styles de commentaires sont possibles -- celui-ci requiert un espace après le double tiret /* et celui-ci peut s'intercaller dans une ligne */ # liste les bases de données du serveur show databases; # create db1 create database 'db1' character set 'utf8mb4' collate 'utf8mb4_general_ci'; # create db1 if it doesn't exist yet create database if not exists db1; # supprime ma_base_de_donnees drop database ma_base_de_donnees; # evite l'erreur database ma_base_de_donnees doesn't exist drop database if exists ma_base_de_donnees; # choix d'une base de données use ma_base_de_donnees; |
Renommer ou copier une base de données avec PhpMyAdmin |
Encoding
-- migrer la db vers un encoding utf8 ALTER DATABASE `db1` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- afficher l'encoding et la collation d'une bdd SELECT default_character_set_name, default_collation_name FROM information_schema.schemata WHERE schema_name = "db_name"; |
Size
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema; |
Tables
# liste les tables de la bases de données sélectionnée show tables; # liste les tables de la bases de données MaBdD show tables from MaBdD; # list the columns of the specified table show columns from MyTable; |
Create
create table if not exists user ( id tinyint unsigned auto_increment primary key, -- PK name varchar(10) not null unique, groupId tinyint unsigned not null references group (Id), --FK computedColumn int as (GroupId * 5) persistent, index ux_user_name (name), -- UX constraint fk_user_group_groupId -- another way to create a named FK: FK_<current table>_<foreign table>_<column> foreign key (groupId) -- column of the current table references group (id)), -- foreign table and its column constraint uc_user_name UNIQUE (name), -- another way to create a name UC primary key (Id) -- another way to create a PK character set 'utf8mb4' collate 'utf8mb4_general_ci'; |
Columns with a primary key constraint are implicitly not null and have an index. |
Drop
drop table my_table; # liste toutes les tables commençant par xxx_ dans la bases de données MaBdD select table_name from information_schema.tables where table_schema = 'MaBdD' and table_name like 'xxx_%'; # même requête mais sort le résultat sur une seule ligne avec , comme séparateur select group_concat(table_name) from information_schema.tables where table_schema = 'MaBdD' and table_name like 'xxx_%'; # affiche en sortie la commande qui permet de drop toutes les tables commençant par xxx_ dans la bases de données MaBdD select concat('drop table ', group_concat(table_name), ';') from information_schema.tables where table_schema = 'MaBdD' and table_name like 'xxx_%'; # drop toutes les tables commençant par xxx_ dans la bases de données MaBdD SET @TABLES = (SELECT GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema = 'MaBdD' and table_name LIKE 'xxx_%'); PREPARE drop_statement FROM 'DROP TABLE @tables'; EXECUTE drop_statement USING @TABLES; DEALLOCATE PREPARE drop_statement; |
Alter
# add a new column with a foreign key alter table `ma_table` add `new_col` varchar(32) not null default 'default value' after `my_col`, add foreign key (`new_col`) references `other_table` (`other_col`); # modify my_column to be datetime not null alter table `my_table` modify `my_column` datetime not null; # rename the table rename table `old_name` to `new_name`; # rename column alter table `my_table` rename column `current_name` to `new_name`; # supprimer « ma_colonne » dans « ma_table » alter table `ma_table` drop `ma_colonne`; # add / remove a unique constraint alter table `my_table` add constraint `uc_my_table_my_column` unique (`my_column`); alter table `my_table` drop constraint `uc_my_table_my_column`; # foreign key alter table `my_table` add constraint `fk_my_table_other_table_my_column` foreign key (`my_column`) references `other_table` (`other_column`); |
Comptes utilisateur
# Syntaxe : 'user_name'@'host_name' # 'user_name' seul est équivalent à 'user_name'@'%' # % est un joker qui signifie n'importe quel hôte # créé mon_compte create user 'mon_compte'@'localhost' identified by 'mon_mot_de_passe'; # créé un compte qui s'authentifie automatiquement sans mdp si le nom d'utilisateur est le même create user 'mon_compte'@'localhost' identified with 'unix_socket'; # Supprime mon_compte drop user 'mon_compte'@'localhost'; # lister les utilisateurs select host, user, password, plugin from mysql.user; # grant read / write privileges on MyDb to MyUser grant select,insert,update on MyDb.* to 'MyUser'@'localhost'; # accorde tous les droits à mon_compte sur la base ma_base_de_donnees grant all on ma_base_de_donnees.* to 'mon_compte'@'localhost'; # accorde tous les droits à mon_compte sur toutes les bdd grant all on *.* to 'mon_compte'@'localhost' with grant option; # with grant option: gives the user the ability to give to other users any privileges the user has at the specified privilege level. # supprime tous les droits d'un utilisateur sur une base revoke all privileges on ma_base_de_donnees.* from 'mon_compte'@'localhost'; # liste les droits de mon_compte show grants FOR 'mon_compte'@'localhost'; # changer un mot de passe set password for 'mon_compte'@'localhost' = password('...'); # ERROR 1133 (28000): Can't find any matching row in the user table flush privileges; |
Échapper les \ avec un deuxième \ dans les mots de passe. |
Reset password without access
sc-stop mysql # set options to run without granting the tables and networking check sudo systemctl set-environment MYSQLD_OPTS="--skip-networking --skip-grant-tables" sc-start mysql # connect without password sudo mysql -u root mysql |
flush privileges; # reset password set password for 'mon_compte'@'localhost' = password('...'); flush privileges; exit; |
# unset options sudo systemctl unset-environment MYSQLD_OPTS # remove the modified system configuration sudo systemctl revert mysql sudo killall -u mysql sudo systemctl restart mysql.service # login mysql -u mon_compte -p |
Account with empty username may mask the user you use to login. You should drop all the account with empty user names. |
Data types
Type | Bytes | Minimum Value | Maximum Value |
---|---|---|---|
TINYINT | 1 | -128 | 127 |
SMALLINT | 2 | -32'768 | 32'767 |
MEDIUMINT | 3 | -8'388'608 | 8'388'607 |
INT | 4 | -2'147'483'648 | 2'147'483'647 |
BIGINT | 8 | -9'223'372'036'854'775'808 | 9'223'372'036'854'775'807 |
Type | Max Length | Description |
---|---|---|
VARCHAR | 65'532 | A variable-length string. The range of the max lenght is 0 to 65'532 or 64KB. utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21'844 characters. |
CHAR | 255 | A fixed-length string, right-padded with spaces to the specified length. The range of the lenght is 0 to 255. |
LONGTEXT | 4GB | A TEXT column with a maximum length of 4,294,967,295 or 4GB characters. |
Data Type | “Zero” Value | Range |
---|---|---|
DATE | '0000-00-00' | '1000-01-01' to '9999-12-31' |
TIME | '00:00:00' | |
DATETIME | '0000-00-00 00:00:00' | '1000-01-01 00:00:00' to '9999-12-31 23:59:59' |
TIMESTAMP | '0000-00-00 00:00:00' | '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC |
YEAR | 0000 |
Date and Time Functions
set @today = NOW(); set @first_day = DATE_FORMAT(@today, '%Y-%m-01'); set @last_day = LAST_DAY(@today); set @previous_month = @today - INTERVAL 1 MONTH; set @diff_in_days = DATEDIFF(@date1, @date2); -- compare only date part set @diff_in_days = TIMESTAMPDIFF(DAY, @date2, @date1); -- compare the whole datetime set @formatted_date = DATE_FORMAT(@date1, '%e %b %Y %H:%i'); -- 1 Jan 2021 23:59 |
Enum
create table if not exists table1 ( type Enum('buy', 'sell') not null -- 0='', 1=buy, 2=sell ); select * from table1 where type = 2; -- sell |
Ip address
create table iptable ( ip binary(4) not null ); insert into iptable (ip) values (inet6_aton('127.0.0.1')); select inet6_ntoa(ip) FROM iptable; |
Charset et Collation
-- afficher le charset et la collation d'une bdd USE MyDb; SELECT @@character_set_database, @@collation_database; -- afficher le charset et la collation de toutes les tables d'une bdd SHOW TABLE STATUS; -- afficher la collation de toutes les colonnes d'une table SHOW FULL COLUMNS FROM <table_name>; -- changer le charset et la collation d'une bdd ALTER DATABASE <db_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; -- changer le charset et la collation d'une table ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; -- changer le charset et la collation d'une colonne ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; |
Ajouter / modifier des données
Insert
insert into my_table (column1, column2) values ('value1-1', 'value1-2'), ('value2-1', 'value2-2'); insert into table1 (column1, column2) select column3, ( select id from table3 where name = 'xxx' ) as table3_id from table2; -- insert if not exists insert into table1 (column1, column2) select 'value1', 'value2' where not exists ( select * from table1 where column1 = 'value1' ); -- set the last inserted id in the @id variable SET @id = LAST_INSERT_ID(); |
Update
update my_table set column1 = 10, column2 = column2 + 1 where column3 = 0; -- update some rows only with values from another table and filter with a query on another table update table1 as dest, ( select column1, column2 from table2 ) as src set dest.column1 = src.column1 where dest.column2 = src.column2; -- update some rows only, filter with a join to another table update table1 as t1 join table2 as t2 on t2.t1_id = t1.id set t1.column1 = 'value1' where t2.column1 = 'value2'; |
Delete
delete from my_table where column1 = 'value1'; truncate table my_table; -- table keyword is optional |
Trigger
-- when rows are inserted in MyTable, update AnotherTable CREATE TRIGGER MyTrigger AFTER INSERT ON MyTable FOR EACH ROW UPDATE AnotherTable SET Column1 = NEW.Column1 WHERE Column2 = NEW.Column2; -- NEW target the inserted row drop trigger MyTrigger; show triggers like 'MyTable'; -- table names (not trigger names) to match |
Requêtes sur les données
Limiter le nombre de résultats
-- retourne les 10 premières lignes select colonne from ma_table limit 10 |
join
Multi select
select 'Value1', ( select count(*) from table1 ) as count_table1; |
With rollup
Over / Window function
SELECT year, country, product, profit, SUM(profit) OVER() AS total_profit, -- sum of all profits for all the countries SUM(profit) OVER(PARTITION BY country) AS country_profit -- sum of profits by country FROM sales ORDER BY country, year, product, profit; |
Where and alias
Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined. (doc) |
select Column1 as c1 from MyTable where c1 > 10 -- Error Code: 1054. Unknown column 'c1' in 'where clause' -- use having instead of where select Column1 as c1 from MyTable having c1 > 10 |
WHERE is applied before GROUP BY, HAVING is applied after. |
Common Table Expressions CTE
WITH cte AS ( SELECT a FROM table1 WHERE b >= 'c' ) SELECT * FROM table2, cte WHERE table2.c = cte.a; |
Temp table
Temporary tables are session specific, they are dropped automatically when the connection is closed.
DROP TEMPORARY TABLE IF EXISTS temp1; CREATE TEMPORARY TABLE temp1 ( column1 VARCHAR(255), column2 INT ); CREATE TEMPORARY TABLE temp2 SELECT * FROM table2 LIMIT 0; INSERT INTO temp1 SELECT 'Value1', 1; DROP TEMPORARY TABLE temp1; |
Configuration
# exécutez le script de configuration sudo mysql_secure_installation |
Pour une utilisation avec php, activez l'un des trois modules:
/etc/php/php.ini |
extension=mysqli.so ;extension=pdo_mysql.so ;extension=mysql.so |
|
/etc/mysql/my.cnf |
[client] # Définit le port par défaut port = 3306 # autorise seulement les connexions depuis la machine hôte skip-networking [mysqld] skip-external-locking max_connections = 200 read_buffer_size = 1M sort_buffer_size = 1M # Set key_buffer to 5 - 50% of your RAM depending on how much you use MyISAM tables, # but keep key_buffer_size + InnoDB buffer pool size < 80% of your RAM key_buffer_size = 512M |
InnoDB
/etc/mysql/my.cnf |
innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:12M:autoextend:max:500M innodb_log_group_home_dir = /var/lib/mysql # You can set .._buffer_pool_size up to 50 - 80 % of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1G # innodb_additional_mem_pool_size was deprecated in MySQL 5.6.3 and removed in MySQL 5.7.4. #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 250M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 |
innodb_buffer_pool_size
-- give how many actual GB of memory is in use by InnoDB Data in the InnoDB Buffer Pool at this moment -- run it after 1 week or 2 of usage SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM (SELECT variable_value PagesData FROM information_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_data') A, (SELECT variable_value PageSize FROM information_schema.global_status WHERE variable_name='Innodb_page_size') B; |
Variables
show global status; show variables; |
Replication basée sur les global transaction identifiers (GTIDs)
Replication de données de la bdd master vers une bdd slave en asynchrone.
Log
# afficher les messages de log mysql depuis le boot journalctl -b | grep mysqld # afficher les messages en temps réel journalctl -f # et redémarrer mysqld sudo systemctl restart mysqld |
-- supprime tous les logs sauf ces des 3 derniers jours PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND; |
Gérer la taille des logs
/etc/mysql/my.cnf |
# limite la taille des fichiers /var/lib/mysql/mysql-bin.xxxxxx expire_logs_days = 10 # default 0 max_binlog_size = 100M # default 1G # fichiers /var/lib/mysql/ib_logfile0 et /var/lib/mysql/ib_logfile1 innodb_log_file_size = 64M # innodb_log_file_size ≈ 25 % of buffer pool size # innodb_buffer_pool_size ≈ 50 - 80 % of RAM # désactive le binary logging en commentant les lignes suivantes # attention plus de replication ni de data recovery possible #log-bin=mysql-bin #binlog_format=mixed |
Audit
Permet de loguer toutes les requêtes.
-- afficher les variables de log SHOW VARIABLES LIKE "general_log%"; -- définir le fichier de log l'audit SET GLOBAL general_log_file = '/var/run/mysqld/mysqld.log'; -- démarrer l'audit SET GLOBAL general_log = 'ON'; -- arrêter l'audit SET GLOBAL general_log = 'OFF'; |
Install
sudo pacman -S mariadb # You need to initialize the MariaDB data directory prior to starting the service. sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql # démarrer mysql pour test sc-start mysqld # change le mot de passe root, autorise ou non l'accès distant avec le compte root, # supprime ou pas la base de données de test et l'utilisateur anonyme créé par défaut. # Recommandé pour un serveur de production. sudo mysql_secure_installation |
Maintenance
# vérifie toutes les table d'une base de données InnoDB mysqlcheck -u root -p --check --databases [nom de la base de données] |
Erreurs
Specified key was too long; max key length is 767 bytes
Les indexes InnoDB ont une taille maximale de 767 octets.
Encoding | Nombre d'octets par caractère | Exemple |
---|---|---|
latin1 | 1 | varchar(255) → 255 octets |
utf8 | 3 | varchar(255) → 765 octets |
utf8mb4 | 4 | varchar(191) → 764 octets |
Error: 145 Table is marked as crashed and should be repaired
# vérifie Ma_Table check table Ma_Table; # répare Ma_Table repair table Ma_Table; |
# check toutes les tables d'une base de données mysqlcheck MaBaseDeDonnées -u Utilisateur -pMotDePasse |
1 client is using or hasn't closed the table properly
# vérifie toute les tables de Ma_Base_de_Donnée et liste celles qui posent problème myisamchk --silent --fast /var/lib/mysql/Ma_Base_de_Donnée/*.MYI # --silent → Only print errors. # --fast → Check only tables that haven’t been closed properly. # check all MyISAM tables and repair any that are corrupted myisamchk --silent --force --fast --update-state \ --key_buffer_size=64M --sort_buffer_size=64M --read_buffer_size=1M --write_buffer_size=1M /var/lib/mysql/Ma_Base_de_Donnée/*.MYI |
La commande myisamchk doit être lancée avec l'utilisateur root afin d'avoir accès au contenu du dossier /var/lib/mysql |
MySQL Workbench
MySQL Workbench : design de BdD
PK | Primary Key |
NN | Not Null |
BIN | Binary (stores data as binary strings. There is no character set so sorting and comparison is based on the numeric values of the bytes in the values.) |
UN | Unsigned (non-negative numbers only. so if the range is -500 to 500, instead its 0 - 1000, the range is the same but it starts at 0) |
UQ | Create/remove Unique Key |
ZF | Zero-Filled (if the length is 5 like INT(5) then every field is filled with 0’s to the 5th value. 12 = 00012, 400 = 00400, etc. ) |
AI | Auto Increment |
G | Generated column. i.e. value generated by a formula based on the other columns |
Convertir une table de MyISAM à InnoDB
alter table ma_table engine=InnoDB; -- génération du script pour migrer toutes les tables d'une base de données SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = 'ma_database' AND `ENGINE` = 'MyISAM' AND `TABLE_TYPE` = 'BASE TABLE' ORDER BY table_name; |
Windows
- C:\Program Files\MariaDB 11.3\data\my.ini
Named pipe authentication
Allow a connection with the current Windows user name. Equivalent to the UNIX SOCKET.
# install the plugin INSTALL SONAME 'auth_named_pipe'; # check if named pipe authentication is enabled SHOW GLOBAL VARIABLES LIKE 'named_pipe'; # create a new user which will identified himself with the named pipe CREATE USER [WindowsUser]@localhost IDENTIFIED VIA named_pipe; |
C:\Program Files\MariaDB 11.3\data\my.ini |
[mysqld] # enable name pipe with pipe named MARIADB enable-named-pipe socket=MySQL |
# connection to the database mariadb --protocol=PIPE # the current Windows user is used and the default pipe name MySQL |
REM get the current user echo %USERNAME% |
Connection String: "server=localhost;database=MyDb;user=WindowsUser;protocol=pipe" |
Features by version
Version | Features |
---|---|
10.6 |
|
10.2 |
|