PHP, SQL & MySQL howto
What is the best collation to use for MySQL (with PHP)
How to change Collation to utf8_bin in one go
mysql check collation of a table
This command shows information about a table, including the collation:
SHOW TABLE STATUS
You'll simply need to run an ALTER on each of the tables as follows:
ALTER TABLE table_name COLLATE utf8_bin;
other example:
ALTER TABLE table_name COLLATE utf8_general_ci;
If you also need to update the existing character encoding (unlikely by the sounds of things), you can use:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
other example:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Update the existing character encoding for a individual table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;
To set default collation for the whole database:
ALTER DATABASE database_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER 'jeffrey'@'localhost';
Create user and set user's password:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
Delete user:
DROP USER 'jeffrey'@'localhost';
or
delete from mysql.user WHERE User='name';
On MySQL 5.7.6 and later:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
On MySQL 5.7.5 and earlier:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');
That is equivalent to the following statements:
UPDATE mysql.user SET Password=PASSWORD('cleartext password') WHERE User='bob' AND Host='%.example.org';
FLUSH PRIVILEGES;
Another way to set the password is to use GRANT:
GRANT USAGE ON *.* TO 'bob'@'%.example.org' IDENTIFIED BY 'cleartext password';
select User from mysql.user;
GRANT ALL PRIVILEGES ON *.* TO 'jeffrey'@'localhost' WITH GRANT OPTION
select user,host from mysql.user;
To show privileges:
show grants for 'user'@'host';
To show privileges for all users:
select * from information_schema.user_privileges;
or
SELECT sql_grants FROM common_schema.sql_show_grants;
To change privileges, first revoke. Such as:
revoke all privileges on *.* from 'user'@'host';
Then grant the appropriate privileges as desired:
grant SELECT,INSERT,UPDATE,DELETE ON `db`.* TO 'user'@'host';
or
GRANT USAGE ON *.* TO 'jeffrey'@'localhost'; (This is the default setting for non-admin users)
Finally, flush:
flush privileges;
Info on flush privileges:
Reloads the privileges from the grant tables in the mysql database.
The server caches information in memory as a result of GRANT and CREATE USER statements. This memory is not released by the corresponding REVOKE and DROP USER statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.
Quickly adding text to a mysql text field that is NOT empty
MySQL concat() function
MySQL CONCAT Function
String concatenation with MySQL - Part 1
MySql add data to existing string
add text to an existing cell in phpmyadmin
UPDATE table_name SET column_name = CONCAT(column_name, ' This will be added.');
UPDATE table_name SET column_name = CONCAT(column_name, ' This will be added.') WHERE value ='something';
UPDATE table_name SET column_name = NULL WHERE column_name is not null;
DELETE FROM table_name WHERE column_name = some_value;