====== mySQL ======

[[mysql:dump|DB-Dump und Import]]

[[mysql:codepage|Zeichensatz und Konvertierung]]

[[mysql:reset_password|Root-Passwort zurücksetzen]]

[[mysql:auto_increment|Auto-Increment]]


[[http://dev.mysql.com/doc/refman/5.0/en/string-functions.html|STRING-functions of mySQL]]

===== Interne Variable zum Sortieren von Listen =====

Im Klassenlisten etc. neu zu sortieren, kann man SQL-interne Variablen nutzen.

Beispiel:

<code>
SET @t1=0; UPDATE `qf_classlist` SET person_number=(@t1:=@t1+1) WHERE `jobnumber` = 1237 AND `class` = 'nichtda' ORDER by remark DESC, name LIMIT 53
</code>

Wenn wir schon dabei sind: Bei Aufträgen allen zugewiesenen Bildern sagen, dass sie den gleichen Klassennamen haben sollen wie die Klassenliste:
<code>
UPDATE `qf_pictures` AS p INNER JOIN qf_picture_relation as r ON (p.id=r.picture_id) INNER JOIN qf_classlist as c ON (c.id=r.classlist_id) SET p.classname = c.class WHERE p.jobnumber = [jobnumber] AND p.classname!=c.class
</code>



Mysql: Passwort vergeben (Root hat noch keins!)
mysql -u root

Geben Sie die folgenden Statements im MySQL-Client ein (ersetzen Sie dabei "IhrNeues Passwort" mit dem von Ihnen gewählten Passwort!):

<code>
mysql> UPDATE mysql.user SET Password=PASSWORD('IhrNeuesPasswort')
    ->                   WHERE User='root';
mysql> FLUSH PRIVILEGES;
PASSWORD('neues Passwort') ist eine Funktion und muss auch so eingegeben werden
</code>

Problem: Debian nutzt einen mysql-User namens 'debian-sys-maint'. Der wurde 
bei meiner Portierung von DB-Dump anscheinend überschrieben und nach einem Neustart
(vorher ging es!) konnte sich niemand mehr in mysql einloggen.
Habe versucht:
<code>
apt-get remove mysql-common mysql-server mysql-client
mv /etc/mysql /etc/mysql.old
apt-get install mysql-common mysql-server mysql-client
</code>
Half nichts.
Dann:
<code>
apt-get install mysqladmin
mysqladmin -u root password 'HIER_PASSWORD_REINSCHREIBEN'
</code>
Half auch nichts.
Nochmal:
Folgendes umbenannt:
<code>
/usr/share/mysql/ 
 /etc/init.d/mysql
 /etc/mysql
 /etc/mysql/mysqlaccess.conf
 /etc/mysql/my.cnf
 /etc/logrotate.d/mysql
 /var/db/pkg/dev-db/mysql*
 /var/lib/mysql (lassen, da hier die DBs sind und ohne mag er nicht starten.)
</code>
Da SQL nicht starten kann, kann ich den Dämon manuell starten (vorher wieder
alle Dateien umbenennen) - ohne Privilegien-Tabelle!:
mysqld --skip-grant-tables
dann. 
<code>
select host,user from user;
</code>
hier sieht man das Problem: Es gibt 4(!) roots. 2 davon vom 'printserver'
Dort ein beherztes:
<code>
DELETE FROM mysql.user WHERE host != 'localhost';
</code>
es geht! - bis zum Neustart. Dann das Problem: mysql startet nicht.
Nochmal:
Wenn dort bei root nicht der Host localhost steht, musste den mit dem GRANT-Befehl setzen. Weitere Informationen zum GRANT-Befehl findest du in der MySQL-Referenz.
Es ging dann...
Kleines Problem:
der user 'debian-sys-maint' kann sich beim systemstart nicht anmelden. Lösung:
Passwort steht in /etc/mysql/debian.conf Merken(kopieren), dann
Einloggen in mysql (als root). 
<code>
UPDATE mysql.user SET password=PASSWORD('gemerktesPasswort') WHERE User='debian-sys-maint';FLUSH PRIVILEGES;
</code>

Hatte die Meldung nach Start: Checke Tables, etc. Problem komtm wohl vom mysl-Versionswechel.
<code>
mysqlcheck --check-upgrade --all-databases --auto-repair --password=[pw]
mysql_fix_privilege_tables --password=[pw]
</code>

SB exportieren (aus alten Server):
<code>
scp -P 22 backup/qunit1.sql root@192.168.123.196:/root/
Dann halt: mysql -u root -p qunit1 < qunit1.sql
</code>



===== Trigger =====
http://dev.mysql.com/doc/refman/5.1/de/create-trigger.html

BEISPIEL encrypt: http://stackoverflow.com/questions/4275882/how-to-encrypt-a-specific-column-in-a-mysql-table

===== Renaming Databases safely =====

Quelle((http://moinne.com/blog/ronald/mysql/rename-a-mysql-database-schema-safely))

This example uses a backup to rename a database in a MySQL instance.

It takes only 5 easy steps:

First copy the schema (db1) with mysqldump:

<code>% mysqldump db1 > db1.sql</code>

Create the a new schema (db2):

<code>% mysqladmin CREATE db2</code>

Insert the backup into the new schema:

<code>% mysql db2 < db1.sql</code>

Drop the old schema:

<code>% mysqladmin drop db1</code>

Now update the user rights:
<code>mysql> UPDATE db SET Db = 'db2' WHERE Db = 'db1';
mysql> UPDATE tables_priv SET Db = 'db2' WHERE Db = 'db1';
mysql> UPDATE columns_priv SET Db = 'db2' WHERE Db = 'db1';
mysql> UPDATE host SET Db = 'db2' WHERE Db = 'db1';</code>

Done! That was easy wasn’t it?

===== Tricks =====

==== Tauschen von Werten in einer Zelle ====

Beispiel: In der DB steht: Berlin 12455, es soll aber stehen: 12455 Berlin (im Feld address1)



Test:
<code>
SELECT id, class, name, address1, address2, CONCAT (SUBSTRING_INDEX( `address1` , ' ', -1 ), ' ', SUBSTRING_INDEX( `address1` , ' ', 1 ))  FROM `qf_classlist` WHERE `jobnumber` = 2061 AND `address1` LIKE '%Berlin%' AND address1 != ''
</code>

<code>
UPDATE `qf_classlist` SET address1 = CONCAT (SUBSTRING_INDEX( `address1` , ' ', -1 ), ' ', SUBSTRING_INDEX( `address1` , ' ', 1 )) WHERE `jobnumber` = 2061 AND `address1` LIKE '%Berlin%' AND address1 != ''
</code>

Tausche Vor/Nachnamen in Klassenliste (Karl, Maier statt Maier, Karl). (Vorname & Nachname sind vertauscht)

<code>
UPDATE `qf_classlist` SET name = CONCAT (SUBSTRING_INDEX( `name` , ', ', -1 ), ', ', SUBSTRING_INDEX( `name` , ', ', 1 )) WHERE `jobnumber` = 2710 
</code>



==== Feld auf einh. Länge bringen (durch führende Nullen) ====

Beispiel: Im Feld code steht 45, soll aber 0000045

<code>
UPDATE `qf_classlist` SET code =  CONCAT((REPEAT("0",(7-LENGTH(code)))), code) WHERE `jobnumber` = 2127 AND LENGTH(code) != 7
</code>



==== Versehentlich doppelt importierte Namen kürzen ====

Beispiel: Meier, Chris, Meier, Chris statt Meier, Chris

<code>
UPDATE `qf_classlist` SET name = SUBSTRING_INDEX(name, ',', 2) WHERE `jobnumber` = [jobnumber] AND class=[class]
</code>

==== Bei einem Auftrag den Bildstatus aller Bilder entfernen ====

Beispiel: Bei Auftrag 2303 soll ein gewisser Status [status] entfernt werden. Aber nur bei Personenbildern.
<code>
UPDATE `qf_pictures` SET status = (status - [status]) WHERE `jobnumber` = 2303 AND (status | [status]) = status AND (content = 1 OR content = 2 OR content = 4 OR content = 2097152)
</code>

ZB: Bit 3 ( = 4 = PICSTATUS_COLORPROOFED) entfernen:
<code>
UPDATE `qf_pictures` SET status = (status - 4) WHERE `jobnumber` = 2358 AND (status | 4) = status AND (content = 1 OR content = 2 OR content = 4 OR content = 2097152)
</code>

==== Klassenliste nachträglich löschen, bei der schon NB-Nummern erstellt wurden (aber keine Bilder) ====

Beispiel: Bei Auftrag 2363 ist die Klassenliste falsch, sie soll gelöscht werden. Es wurden aber schon NB-Nummern reserviert. Die sollen auch weg, weil die Nummern sonst unnütz verschwendet werden

1. NB-Nummern löschen:
<code>
DELETE ro.* FROM qf_reorder AS ro INNER JOIN qf_picture_relation AS r ON (ro.id=r.reorder_id) INNER JOIN `qf_classlist` as c ON (c.id=r.classlist_id) WHERE c.jobnumber = 2363
</code>

2. Dann die Relations:
<code>
DELETE r.* FROM qf_picture_relation AS r INNER JOIN `qf_classlist` as c ON (c.id=r.classlist_id) WHERE c.jobnumber = 2363
</code>

3. Schliesslich die KL selbst:
<code>
DELETE c.* FROM `qf_classlist` as c WHERE c.jobnumber = 2363
</code>

==== Alle Bilder löschen( wenn sie versehentlich importiert wurden) ====
Besonderes Problem: Nachbestellnummern sind schon erstellt (ohne Bilder), ohne dass Bilder da sin. - weil den Schülern die Nummer schon mitgeteilt wurde.

Schauen, welche neuen (jetzt falschen) Relations erstellt wurden:
Die erkennt man daran, dass sie noch keine Reorder-Nummer haben:
<code>
SELECT * FROM qf_picture_relation AS r INNER JOIN `qf_classlist` as c ON (c.id=r.classlist_id) WHERE c.jobnumber = 123 AND picture_id>0 
</code>

1. Die neu erstellten Relations löschen (sind jetzt überflüssig):
<code>
DELETE r.* FROM qf_picture_relation AS r INNER JOIN `qf_classlist` as c ON (c.id=r.classlist_id) WHERE c.jobnumber = 123 AND r.picture_id>0 AND r.reorder_id=0
</code>

2. Jetzt die Relations, die schon eine Picture-ID haben wieder auf 0 setzen:
<code>
UPDATE qf_picture_relation AS r INNER JOIN `qf_classlist` as c ON (c.id=r.classlist_id) SET r.picture_id=0 WHERE c.jobnumber = 123 AND r.picture_id>0
</code>

3. Schliesslich die DB-Einträge der Bilder löschen:
<code>
DELETE FROM qf_pictures WHERE jobnumber = 123
</code>

und die Bilder im Archive löschen:
<code>
rm /var/archive/123/*
</code>

==== Alle Klassenlisten löschen ( wenn schon Barcodes/Nachbestellnummern erstellt wurden) ====
Besonderes Problem: Nachbestellnummern sind schon erstellt (ohne Bilder), ohne dass Bilder da sind. - den Schülern wurden die Nummern NICHT mitgeteilt. Jetzt sind die KLL aber falsch und müssen durch neue ersetzt werden. Die alten NB-Nummer werden __auch__ gelöscht.


Erst die NB-Nummern löschen:
<code>
DELETE ro.* FROM qf_reorder as ro INNER JOIN qf_picture_relation AS r ON (r.reorder_id=ro.id) INNER JOIN `qf_classlist` as c ON (c.id=r.classlist_id) WHERE c.jobnumber = XXX
</code>

Dann die dazugehörigen Relations löschen:
<code>
DELETE r.* FROM qf_picture_relation AS r INNER JOIN `qf_classlist` as c ON (c.id=r.classlist_id) WHERE c.jobnumber = XXX
</code>

Dann die KLL selbst:
<code>
DELETE FROM `qf_classlist` WHERE `jobnumber` = XXX
</code>

==== Auf nicht existierende Klassenlisteneinträge zeigende NB-Nummern suchen/ändern ====

Wenn die Klassenliste gelöscht wurde, aber schon Nachbestellnummern erzeugt wurden, kann es sein, dass NAchbestelleinträge ins Nichts führen.

So kann man diese Einträge finden:

<code>
SELECT r.*, pr.classlist_id as CID FROM `qf_reorder` as r JOIN qf_picture_relation as pr ON r.id = pr.reorder_id LEFT JOIN qf_classlist as c ON pr.classlist_id = c.id WHERE pr.picture_id = 0 AND pr.classlist_id >0 AND c.id IS NULL ORDER BY pr.classlist_id
</code>

Haben die alten und die neuen CIDs einen bestimmten Abstand X, kann man die Relations updaten (man sollte wissen, in welchen Bereich die gelöschten CIDs lagen):

(MariaDB)
<code>

SELECT * FROM qf_picture_relation as pr JOIN qf_reorder as r ON r.id = pr.reorder_id LEFT JOIN qf_classlist as c ON pr.classlist_id = c.id WHERE pr.picture_id = 0 AND pr.classlist_id >0 AND c.id IS NULL AND pr.classlist_id >= 450764 AND pr.classlist_id <= 450833 ORDER BY pr.classlist_id LIMIT 71

UPDATE qf_picture_relation as pr JOIN qf_reorder as r ON r.id = pr.reorder_id LEFT JOIN qf_classlist as c ON pr.classlist_id = c.id SET classlist_id = (classlist_id + X) WHERE pr.picture_id = 0 AND pr.classlist_id >0 AND c.id IS NULL AND pr.classlist_id >= 450764 AND pr.classlist_id <= 450833 LIMIT 71 
</code>


==== Problem: Klassenliste wurde gelöscht, aber es waren DOCH schon Bilder an diese Klassenlisten gekoppelt ====

Das heißt, die Bilder werden als nicht zugewiesen in Kuskus angezeigt, sind aber schon auf (nicht mehr existierende) Klassenlisten zugweisen. 

Diese erst mal suchen:

<code>
SELECT r.*, r.classlist_id AS CID, p.date_delete, p.jobnumber FROM qf_picture_relation AS r JOIN qf_pictures as p ON r.picture_id = p.id LEFT JOIN qf_classlist as c ON r.classlist_id = c.id WHERE r.classlist_id > 0 AND c.id IS NULL AND !(r.reorder_id > 0) AND p.date_delete > DATE(NOW()) ORDER BY r.classlist_id
</code>

Reparieren (hier für #2905 und Limit 250):

<code>

UPDATE qf_picture_relation AS r JOIN qf_pictures as p ON r.picture_id = p.id LEFT JOIN qf_classlist as c ON r.classlist_id = c.id SET r.classlist_id = 0 WHERE p.jobnumber = '2905' AND r.classlist_id > 0 AND c.id IS NULL AND !(r.reorder_id > 0) AND p.date_delete > DATE(NOW()) LIMIT 250

</code>


==== 1 Feld: alles klein schreiben, nur 1. Buchstabe groß ====

Alle Einträge eines Feldes sind groß (oder klein) geschrieben. Es soll aber nur der 1. Buchstabe jedes Namens (nach einem Leerzeichen) groß sein, der Rest klein.

Erst eine Funktion definieren: Quelle((https://www.tutorials.de/threads/mysql-erster-buchstabe-gross-rest-klein.367889/))
<code>
DELIMITER ||
DROP FUNCTION IF EXISTS ucwords||
CREATE FUNCTION ucwords(s VARCHAR(255)) RETURNS VARCHAR(255) BEGIN
   DECLARE cont INT UNSIGNED DEFAULT 0;
   DECLARE flag TINYINT UNSIGNED DEFAULT 1;
   DECLARE cadena VARCHAR(255) DEFAULT '';
 
   WHILE(cont <= LENGTH(s)) DO
      IF(SUBSTRING(s,cont,1) = ' ') THEN
         SET cadena = CONCAT(cadena,' ');
         SET cont = cont + 1;
         SET flag = 1;
      ELSE
         SET flag = 0;
      END IF;
      IF flag = 1 THEN
         SET cadena = CONCAT(cadena,UCASE(SUBSTRING(s,cont,1)));
      ELSE
         SET cadena = CONCAT(cadena,LCASE(SUBSTRING(s,cont,1)));
      END IF;
      SET cont = cont + 1;
   END WHILE;
 
   RETURN LTRIM(cadena);
END;
||
DELIMITER ;
</code>

Dann diese Funktion auf das Feld anwenden:

<code>
UPDATE `qf_classlist` SET name = ucwords(name) WHERE jobnumber = 2352
</code>
Achtung: aus 'VON DER BELLEN' wird 'Von Der Bellen'

Dieser einfache Befehl macht nur den allerersten Buchstaben im Feld groß, den Rest klein:

<code>
SELECT id, CONCAT(UPPER(LEFT(name, 1)), RIGHT(LOWER(name), CHAR_LENGTH(name) - 1)) FROM `qf_classlist` WHERE jobnumber = 2352
</code>

==== Klassenliste nach Bild-ID ordnen ====

Anwendungsfall: Eine höhere Klasse ist nach Kursen fotografiert. Die Klassenliste kam aber alphabetisch. Nun soll die KLL in der Reihenfolge der Bilder umgestellt werden (nach Importzeitpunkt, also PID), damit man die KLL leicht in die Kurse verschieben kann. 

<code>
UPDATE qf_classlist, qf_picture_relation SET qf_classlist.person_number = qf_picture_relation.picture_id WHERE qf_picture_relation.classlist_id = qf_classlist.id AND qf_classlist.jobnumber = 1234 AND qf_classlist.class = 'ABC 12' AND qf_picture_relation.picture_id > 0 
</code>

==== Fortlaufende Nummern im Barcodes-Feld vergeben ====

Das Barcode-Feld soll fortlaufende Nummern bekommen, beginnend mit 1000:

<code>
SELECT @i:=1000; UPDATE `qf_classlist` SET code = @i:=@i+1 WHERE `jobnumber` = 1234 AND (code IS NULL OR code = '') LIMIT 500
</code>

==== Zeichensatz einer Column einer Tabelle in UTF-8 ändern ====

<code>ALTER TABLE tel_log MODIFY telbook_name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci</code>

==== Doppelte Imports vom Schatzkiste/Fotoaktions-Server finden ====

Kann eigentlich nicht sein, dass ein Tag doppelt importiert wurde (aber manchmal eben schon, weil es einen Fehler gab und es nochmal gemacht werden musste). Folge ist, dass die Statistik nicht mehr ganz stimmt. Kam bislang nur 3X vor.

Zeigt doppelte an: Quelle((https://www.pixelfriese.de/doppelte-eintraege-in-mysql-tabelle-finden/))

<code>
SELECT liste.id, liste.jobnumber, liste.class, liste.name, liste.order_number, liste.value_payed, liste.order_number FROM qf_classlist as liste INNER JOIN (SELECT jobnumber, class, name, order_number, value_payed FROM qf_classlist GROUP BY jobnumber, class, name, order_number HAVING COUNT(id) > 1) dup ON (liste.jobnumber = dup.jobnumber && liste.name = dup.name && liste.class = dup.class && liste.order_number = dup.order_number && liste.value_payed = dup.value_payed) WHERE liste.value_payed > 0 AND liste.order_number != '' ORDER by jobnumber, class, name
</code>


==== Fotokation: Ein Angebot (oder mehrere) an das Ende des Offerstrings anhängen ====

<code>
UPDATE `fotoaction_pics` SET offer = CONCAT(offer, ',163') WHERE `valid_to` > '2025-11-20' AND offer NOT LIKE '%,163' LIMIT 25000
</code>


==== Alle Bestellnummern der Fotoaktion eines Auftrags (z.B. für Excel) ====


<code>
SELECT class, name, orderinfo_key FROM `qf_classlist` WHERE `jobnumber` = 2871 AND `class` != 'Absent' AND `orderinfo_key` != '' ORDER BY class
</code>

==== Die Nachbestellnummern eines Auftrages wurden erstellt/reserviert, sollen aber wieder weg ====

Die Klassenlisten sind drinnen, die Bilder sind noch nicht gemacht. Die NB-Nummern wurden reserviert, die Barcodes sind gedruckt. Fotograf hat keine Lust mehr und ein anderer Fotograf macht den Auftrag. Die alten NB-Nummern müssen weg als Datenschutzgründen und man muss die Reserviertung neu nachen.

Erst mal alle Nachbestellnummern des Auftrags löschen:

<code>
DELETE r 
FROM qf_reorder AS r 
JOIN qf_picture_relation AS rel ON (rel.reorder_id = r.id) 
JOIN qf_classlist AS c ON (rel.classlist_id = c.id) 
WHERE c.jobnumber = 2911;
</code>

Sind sie weg?

<code>
SELECt r.* FROM qf_reorder AS r JOIN qf_picture_relation AS rel ON (rel.reorder_id = r.id) JOIN qf_classlist AS c ON (rel.classlist_id = c.id) WHERE rel.picture_id = 0 AND c.jobnumber = 2911
</code>

Jetzt die Relations löschen: (muss sein! Weil die reorder_id jetzt auf eine nicht-existierende picture_id zeigt!!!)

<code>
DELETE rel 
FROM qf_picture_relation AS rel JOIN qf_classlist AS c ON (rel.classlist_id = c.id) 
WHERE rel.picture_id = 0 AND c.jobnumber = 2911
</code>


Sind sie weg?
<code>
SELECT rel.* 
FROM qf_picture_relation AS rel JOIN qf_classlist AS c ON (rel.classlist_id = c.id) 
WHERE rel.picture_id = 0 AND c.jobnumber = 2911
</code>

==== Groß- & Kleinschreibung bei Abfrage einer Tabelle in 'utf8_unicode_ci', die ausgelegt ist ====

Das ci hinten bedeutet 'case insensitiv'


Ich habe eine Tabelle in einer Datenbank mit der Kollation utf8_unicode_ci. Jetzt möchte ich eine Abfrage machen, die auf Groß- und Kleinschreibung achtet. Wie mache ich das?

Lösungsmöglichkeiten:

  - **BINARY-Keyword verwenden:** Sie können das BINARY-Keyword verwenden, um die Spalte in eine binäre Form umzuwandeln, wodurch die Unterscheidung zwischen Groß- und Kleinschreibung erzwungen wird:

<code>SELECT * FROM tabelle WHERE BINARY spalte = 'Wert';
</code>

  - **Binäre Kollation in der Abfrage festlegen:** Eine andere Möglichkeit ist, die Kollation direkt in der Abfrage zu ändern:

<code>SELECT * FROM tabelle WHERE spalte COLLATE utf8_bin = 'Wert';</code>

  - **Spalte mit einer binären Kollation erstellen:** Wenn die Abfrage häufiger benötigt wird, könnten Sie überlegen, die Spalte dauerhaft mit einer binären Kollation zu speichern:


<code>ALTER TABLE tabelle MODIFY spalte VARCHAR(255) COLLATE utf8_bin;</code>