Archive for the ‘mysql’ Category


MySQL Create new user and grant commands

January 19, 2012

Make a new database (schema) …

mysql> create schema myschema;
Query OK, 0 rows affected (0.00 sec)

Add a new user …

mysql> create user 'fred'@'localhost' identified by 'password-secret-string';
Query OK, 0 rows affected (0.00 sec)

Let the user have access to the new schema …

mysql> grant all privileges on myschema.* to 'fred'@'localhost';
Query OK, 0 rows affected (0.01 sec)


replace syntax in mysql

November 3, 2011

The mysql REPLACE syntax allows you to do string replacements within the content of mysql table text fields. One application I used recently was to rewrite links in blocks of text entered into the Druapl CMS.

UPDATE table SET col = REPLACE(col, '/some/path', '/some/other/path');

This will replace all instances of ‘/some/path’ with ‘/some/other/path’ within the col column of the table table.


Making a field unique after creating a table

December 15, 2010

The MySQL browser on Linux doesn’t appear to let you add unique column indexes to a table in retrospect after the table has been created. There are mysql commands though which allow this:

ALTER TABLE tablename ADD UNIQUE name_of_index (col1,col2...);

Statement to make unique columns without altering table:

CREATE UNIQUE INDEX name_of_index ON tablename (col1,col2,col3...);

Character encoding with PHP and MySQL

September 21, 2010

Today had a problem reading UTF-8 encoded characters from MySQL using PHP. After connecting to the MySQL database I needed to explictly set the character encoding standard, PHP MySQL driver did not seem to automatically know what the db was set to. I am using the PDO method with PHP and MySQL…

$PDO = new PDO('mysql:host=myhost;dbname=db', $username, $password);
$PDO->exec('SET CHARSET SET utf8');