MySQL

Administration

Users

Create user

CREATE USER pokecrawl IDENTIFIED BY '1234';

Delete user

DROP USER pokecrawl;

List users

SELECT USER FROM mysql.user;

Databases

Create database

CREATE DATABASE synthese_rt;

Change database

USE synthese_rt;

Grant rights to database to user

GRANT ALL ON synthese_rt.* TO 'pokecrawl'@'localhost';
FLUSH privileges;

Remove database

DROP DATABASE synthese_rt;

Export content to file

mysqldump -u user -ppassword db_name > dump.sql

Import content from file

mysql -u user -ppassword db_name < dump.sql

Tables

List all tables

SHOW TABLES;

Search for tables

SHOW TABLES LIKE '%drt%';
SHOW COLUMNS FROM t010_line_stops;

Delete column from a table

ALTER TABLE t010_line_stops DROP COLUMN enter_and_exit;

Troubleshooting

Show warnings

SHOW WARNINGS;

Function STRINGDECODE() needed during import from H2

CREATE FUNCTION STRINGDECODE(str TEXT CHARSET utf8)
RETURNS text CHARSET utf8 DETERMINISTIC
BEGIN
DECLARE pos         INT;
DECLARE escape      CHAR(6) charset utf8;
DECLARE unescape    CHAR(3) charset utf8;
SET pos = locate('\\u', str);
while pos > 0 do
    SET escape = SUBSTRING(str, pos, 6);
    SET unescape = CHAR(conv(SUBSTRING(escape,3),16,10) USING ucs2);
    SET str = REPLACE(str, escape, unescape);
    SET pos = locate('\\u', str, pos+1);
END while;
RETURN str;
END

Query

Random number between 100 and 999

ROUND(RAND() * 900) + 100

Concatenation

CONCAT('This ', 15, ' Sparta!);

Row number

SELECT 
    @i:=@i+1 AS iterator, 
    t.*
FROM 
    tablename AS t,
    (SELECT @i:=0) AS foo

Point <-> Text conversion

SELECT AsText(geometry) FROM TABLE_NAME WHERE ...;
UPDATE TABLE_NAME SET geometry = PointFromText('POINT(1 1)') WHERE ...;

Get rid of what comes after a character

UPDATE TABLE_NAME SET FIELD = substring_index(FIELD, ',', 1);

If the value of the “field” column was “hello,world”, after this command, it will be “hello”.

Other examples of substring_index

Input Output
substring_index('a,b,c', ',', 1);
a
substring_index('a,b,c', ',', 2);
a,b
substring_index('a,b,c', ',', 3);
a,b,c
substring_index('a,b,c', ',', -1);
c
substring_index('a,b,c', ',', -2);
b,c
substring_index('a,b,c', ',', -3);
a,b,c
Print/export