MySQL / MariaDB & MongoDB Everything about MySQL / MariaDB and MongoDB 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 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. MD5 generates a 128-bit hash value. You can use CHAR(32) or BINARY(16) SHA-1 generates a 160-bit hash value. You can use CHAR(40) or BINARY(20) SHA-224 generates a 224-bit hash value. You can use CHAR(56) or BINARY(28) SHA-256 generates a 256-bit hash value. You can use CHAR(64) or BINARY(32) SHA-384 generates a 384-bit hash value. You can use CHAR(96) or BINARY(48) SHA-512 generates a 512-bit hash value. You can use CHAR(128) or BINARY(64) BCrypt generates an implementation-dependent 448-bit hash value. You might need CHAR(56), CHAR(60), CHAR(76), BINARY(56) or BINARY(60) 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 CONVERT TO CHARACTER SET COLLATE ; Convert Database to MyISAM #!/bin/shDB=''TABLES=$(/opt/lampp/bin/mysql -uroot --skip-column-names -B -D $DB -e 'show tables')for T in $TABLESdo /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: In mysql prompt, enter SET GLOBAL max_allowed_packet=524288000; Set max_allowed_packet in my.ini Setup for Remote Access Grant Privileges GRANT ALL ON .* TO @'%' IDENTIFIED BY ''; Testing Remote Access mysql -uroot -p -h Basic MongoDB Operations Queries and Indexes Display query stats db..find({}).explain('executionStats') Basic Document Operations Find all documents in a collection db..find({}) Sorting documents db..find({}).sort() Find one document in a collection db..findOne({}) Count documents in a collection db..count() Insert a document db..insert({fieldA: 'a', fieldB: 'b'}) Updating a document db..update({_id: ''}, {'$set': {'fieldA': 'value'}}) Updating multiple documents db..update({: ''}, {'$set': {'fieldA': 'value'}}, {multi: true}) Delete a document db..remove({_id: ''}) Collection Operations Remove a collection db..drop() List all collections show collections Database Operations List all databases show databases Switch to a database use