// May 6th, 2009 // No Comments » // Database, Development, MySQL
As the MySQL USER() command returns users as ‘user’@'host’, it can be difficult to do straight comparasons. One way to extract both username and host is by using SUBSTRING_INDEX. For example, in a stored procedure you could use:
-- USER
SELECT SUBSTRING_INDEX(USER(), '@', 1) INTO txtUsername;
-- HOST
SELECT SUBSTRING_INDEX(USER(), '@', -1) INTO txtHost;
Voila!
// March 24th, 2009 // No Comments » // Database, MySQL
The best way for creating users in MySQL is by using CREATE USER or GRANT statements. Another way is by manipulating MySQLs grant tables, but this is not recommended.
By default the superuser is root. A password must be specified for this. It is best if this is only used for superadmin purposes.
For new accounts you need to create two users with the same name. One is for access via localhost, the other remotely.
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
To remove a user, use
DROP USER user;
To Change a password, use:
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('newpassword');
SET PASSWORD FOR 'user'@'%' = PASSWORD('newpassword');
// March 24th, 2009 // No Comments » // Database, MySQL
Using cmd prompt in Windows, navigate to the bin directory where MySQL has been installed. There will be a file called “mysqldump.exe” in that directory. Enter the following command:
mysqldump -u root -ppass --databases your_db > backup.sql
Notice that there is no space between the -p and the password, this is a bug in MySQL.
The backup.sql file will be created in the directory that you are in. You can specify C:\your_db.sql if you want the file to be backed up on the C:\ drive.
If you want to backup the structure only, use -d for the “–no-data-option”. For example:
mysqldump -u root -ppass -d --databases your_db > backup.sql
// February 17th, 2009 // No Comments » // Database, MySQL
I wanted to copy the contents of an existing table I had which only had certain fields populated.
INSERT INTO new-table (field1, field2)
SELECT distinct original-field1, original-field2
FROM existing-table WHERE original-field1 is not null
// November 12th, 2008 // No Comments » // Database, MySQL, PHP, SQL
Last year I needed to display 3 random featured items. The solution was very simple using MySQL with this query:
SELECT * FROM `table` WHERE `column` = 'criteria' ORDER BY RAND() LIMIT 3;
// September 24th, 2008 // No Comments » // Database, Oracle
Spool prints out the current terminal text to a file
SQL> Spool on
SQL> set heading off <===
SQL> Spool c:\spooltext.txt
SQL> your_Query
SQL> Spool off
