How to do Everything in MySQL/MariaDB

Useful Tricks

Convert a Column to Uppercase

UPDATE table_name SET column_name = UPPER( column_name )


Show and Change View Definer

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
SHOW CREATE VIEW [view_name];
ALTER DEFINER = '[username]'@'[host]' VIEW [view_name] AS [select statement]; 

Alter Views

ALTER VIEW <view name> AS [view statements]

MySQL Datatype for Different Password Hashes

It depends on the hashing algorithm you use. Hashing always produces a result of the same length, regardless of the input. It is typical to represent the binary hash result in text, as a series of hexadecimal digits. Or you can use the [UNHEX()](http://dev.mysql.com/doc/refman/5.5/en/string- functions.html#function_unhex) function to reduce a string of hex digits by half.

NIST recommends using SHA-256 or higher for passwords. Lesser hashing algorithms have their uses, but they are known to be crackable.

You should salt your passwords before applying the hashing function. Salting a password does not affect the length of the hash result.


Chinese Support in MySQL

Convert entire database to UTF-8: ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Convert entire table to UTF-8: ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Convert field to UTF-8: ALTER TABLE tablename MODIFY columnname columndef CHARACTER SET utf8 COLLATE utf8_unicode_ci;


MySQL Grant Permission

Grant all on {dbname}.* to 'Id'@'localhost' identified by 'password'

Convert All Table Columns' Charset and Collation

ALTER TABLE <table> CONVERT TO CHARACTER SET <charset> COLLATE <collation>;

Convert Database to MyISAM

#!/bin/sh

DB='<DB Name>'
TABLES=$(/opt/lampp/bin/mysql -uroot --skip-column-names -B -D $DB -e 'show tables')

for T in $TABLES
do
/opt/lampp/bin/mysql -uroot -D $DB -e "ALTER TABLE $T ENGINE=MYISAM"
done

Solution to Common Problems

MariaDB Function Error From mysqldump

Use DELIMITER keyword to change end of function delimiter, e.g.:

DELIMITER //
CREATE FUNCTION counter () RETURNS INT
BEGIN
UPDATE counter SET c = c + 1;
RETURN (SELECT c FROM counter LIMIT 1);
END;
//
CREATE FUNCTION counter2 () RETURNS INT
BEGIN
UPDATE counter SET c = c + 2;
RETURN (SELECT c FROM counter LIMIT 1);
END;
//
DELIMITER ;

Error while sending QUERY packet

Change your maxallowedpacket by using one of the following methods:


Revision #5
Created 5 April 2017 20:03:27 by Tingwai
Updated 5 April 2017 22:11:58 by Tingwai