-
Archives
- January 2012
- December 2011
- September 2011
- August 2011
- June 2011
- January 2011
- December 2010
- November 2010
- October 2010
- August 2010
- July 2010
- June 2010
- May 2010
- April 2010
- March 2010
- January 2010
- December 2009
- November 2009
- October 2009
- September 2009
- January 2009
- November 2008
- September 2008
- August 2008
- July 2008
- March 2008
- February 2008
- January 2008
-
Meta
Category Archives: MySQL
MySQL regex update
Simple solution: UPDATE table SET words = REPLACE(words, ‘brown’, ‘green’) WHERE words REGEXP ‘brown ‘ OR words REGEXP ‘ brown’ Better solution from http://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/: SET sql_mode=’NO_BACKSLASH_ESCAPES’; DELIMITER $$ CREATE FUNCTION `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000)) RETURNS VARCHAR(1000) DETERMINISTIC BEGIN DECLARE temp … Continue reading
Posted in MySQL
Leave a comment
How to get next MySQL insert id
function getMysqlInsertID($tablename) { $next_increment = 0; $qShowStatus = “SHOW TABLE STATUS LIKE ‘$tablename’”; $qShowStatusResult = mysql_query($qShowStatus) or die ( “Query failed: ” . mysql_error() . “<br/>” . $qShowStatus ); $row = mysql_fetch_assoc($qShowStatusResult); $next_increment = $row['Auto_increment']; return $next_increment; }
mysqldump results to wrong characters
Try the following: $ mysqldump -u<user> -p dbsource tablein –where=”products_id >= 20000028 and products_id <= 25531404″ –default-character-set=UTF8 > dumpfile.sql $ mysql -u<user> -p dbtarget –default-character-set=UTF8 < dumpfile.sql
How to use SQL where like in()
SELECT column FROM table WHERE column LIKE ‘%like1%’ OR column LIKE ‘%like2%’ OR column LIKE ‘%etc2%’ can be written simply as SELECT column FROM table WHERE column REGEXP ‘like1|like2|etc’
selective mysqldump
$ mysqldump -uroot -p DB TBL –where=”COL_ID=3″ | grep INSERT
Extract particular table from a mysqldump file
$ awksqldump dbname.dump.sql tblname #!/bin/bash ### check parameter E_NO_ARGS=65 if [ $# -eq 2 ] then # Outputs string of specified table only awk “/Table structure for table .$2./,/UNLOCK TABLES/{print}” $1 > $1.EXTRACTED.sql elif [ $# -eq 3 ] then … Continue reading
Posted in Bash script, Database, Linux Administration, MySQL
Tagged awksqldump, bash
Leave a comment
How to set a MySQL Table AUTO_INCREMENT
To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this: mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
How to Remove a MySQL Table constraint
ALTER TABLE `attributes` DROP FOREIGN KEY `attributes_ibfk_1`; MySQL ERROR 1005 (HY000): Can’t create table ‘Table.frm’ (errno: 150). Here’s the solution. Example: ALTER TABLE `race` DROP FOREIGN KEY `race_ibfk_1`; ALTER TABLE `race` ADD CONSTRAINT `race_ibfk_1` FOREIGN KEY (`specieId`) REFERENCES `specie` (`specieId`) … Continue reading
How to Create Database and Add User To It
Here are the SQL commands: create database newdb; grant CREATE,INSERT,DELETE,UPDATE,SELECT on newdb.* to newuser@localhost; SET PASSWORD FOR ‘newuser’@'localhost’ = password(‘newuserpass’); – or – SET PASSWORD FOR ‘newuser’@'localhost’ = old_password(‘newuserpass’); flush privileges;