mySQL

DB-Dump und Import

Zeichensatz und Konvertierung

Root-Passwort zurücksetzen

Auto-Increment

STRING-functions of mySQL

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

Beispiel:

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

Wenn wir schon dabei sind: Bei Aufträgen allen zugewiesenen Bildern sagen, dass sie den gleichen Klassennamen haben sollen wie die Klassenliste:

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

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!):

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

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:

apt-get remove mysql-common mysql-server mysql-client
mv /etc/mysql /etc/mysql.old
apt-get install mysql-common mysql-server mysql-client

Half nichts. Dann:

apt-get install mysqladmin
mysqladmin -u root password 'HIER_PASSWORD_REINSCHREIBEN'

Half auch nichts. Nochmal: Folgendes umbenannt:

/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.)

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.

select host,user from user;

hier sieht man das Problem: Es gibt 4(!) roots. 2 davon vom 'printserver' Dort ein beherztes:

DELETE FROM mysql.user WHERE host != 'localhost';

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).

UPDATE mysql.user SET password=PASSWORD('gemerktesPasswort') WHERE User='debian-sys-maint';FLUSH PRIVILEGES;

Hatte die Meldung nach Start: Checke Tables, etc. Problem komtm wohl vom mysl-Versionswechel.

mysqlcheck --check-upgrade --all-databases --auto-repair --password=[pw]
mysql_fix_privilege_tables --password=[pw]

SB exportieren (aus alten Server):

scp -P 22 backup/qunit1.sql root@192.168.123.196:/root/
Dann halt: mysql -u root -p qunit1 < qunit1.sql

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

Quelle¹⁾

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:

% mysqldump db1 > db1.sql

Create the a new schema (db2):

% mysqladmin CREATE db2

Insert the backup into the new schema:

% mysql db2 < db1.sql

Drop the old schema:

% mysqladmin drop db1

Now update the user rights:

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';

Done! That was easy wasn’t it?

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

Test:

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 != ''

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

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

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

Beispiel: Im Feld code steht 45, soll aber 0000045

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

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

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

Beispiel: Bei Auftrag 2303 soll ein gewisser Status [status] entfernt werden. Aber nur bei Personenbildern.

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)

ZB: Bit 3 ( = 4 = PICSTATUS_COLORPROOFED) entfernen:

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)

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:

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

2. Dann die Relations:

DELETE r.* FROM qf_picture_relation AS r INNER JOIN `qf_classlist` as c ON (c.id=r.classlist_id) WHERE c.jobnumber = 2363

3. Schliesslich die KL selbst:

DELETE c.* FROM `qf_classlist` as c WHERE c.jobnumber = 2363

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:

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 

1. Die neu erstellten Relations löschen (sind jetzt überflüssig):

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

2. Jetzt die Relations, die schon eine Picture-ID haben wieder auf 0 setzen:

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

3. Schliesslich die DB-Einträge der Bilder löschen:

DELETE FROM qf_pictures WHERE jobnumber = 123

und die Bilder im Archive löschen:

rm /var/archive/123/*

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:

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

Dann die dazugehörigen Relations löschen:

DELETE r.* FROM qf_picture_relation AS r INNER JOIN `qf_classlist` as c ON (c.id=r.classlist_id) WHERE c.jobnumber = XXX

Dann die KLL selbst:

DELETE FROM `qf_classlist` WHERE `jobnumber` = XXX

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:

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

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)

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 

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:

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

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

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

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²⁾

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 ;

Dann diese Funktion auf das Feld anwenden:

UPDATE `qf_classlist` SET name = ucwords(name) WHERE jobnumber = 2352

Achtung: aus 'VON DER BELLEN' wird 'Von Der Bellen'

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

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

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.

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 

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

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

ALTER TABLE tel_log MODIFY telbook_name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci

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³⁾

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

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

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:

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;

Sind sie weg?

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

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

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

Sind sie weg?

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

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:

1. 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:

SELECT * FROM tabelle WHERE BINARY spalte = 'Wert';

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

SELECT * FROM tabelle WHERE spalte COLLATE utf8_bin = 'Wert';

1. 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:

ALTER TABLE tabelle MODIFY spalte VARCHAR(255) COLLATE utf8_bin;