Category Archives: Programming/Database

anything related to programming stuffs or tools
database related stuffs

MySQL : drop table with prefix

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_schema = 'nama_pengkalandata' AND table_name LIKE 'namaprefix_%';

run those SQL in mysq terminal or phpmyadmin or any mysql client. It will generate ‘drop table’ syntax.

ref: http://stackoverflow.com/questions/1589278/sql-deleting-tables-with-prefix

Mysql show only column names

2 type of sql to show only column name

1.   SELECT column_name
FROM information_schema.columns
WHERE table_schema = ‘DBNAME’
AND table_name = ‘TABLENAME’;

 

2.  SELECT column_name
FROM information_schema.columns
WHERE table_name = ‘TABLENAME’

 

Ref: stackoverflow.com

Mysql Mass Kill

This method I use to do mass kill on mysql process. After run “show processlist” and notice something.

mysql> select concat('KILL ',id,';') from information_schema.processlist
 where Host like '%oss%' and time > 200 into outfile '/tmp/lock.txt';
mysql> source /tmp/lock.txt;

done.. all the process where host like oss and time more than 200.

References:
mysqlperformanceblog, stackoverflow
tested  on centos 6.5 mysql 5.5

Mysql: insert if record not exist (in case no unique key)

here is the sql syntax used to insert record when not exist :-

INSERT INTO pengguna(nokp,nama,idlevel,statusdata) SELECT * FROM ( SELECT ‘888888’,’namasaya,’1000′,’1′ ) AS tmp WHERE NOT EXISTS ( SELECT nokp FROM pengguna WHERE nokp=’999′) LIMIT 1

 

 

mysql : UPDATE table and CONCAT data

Syntax : UPDATE and CONCAT(first,second)

table name : jos_data
+------+------+------------+
| id   | name | images     |
+------+------+------------+
|    1 | John | jonh.jpg   |
|    2 | Ram  | rem.jpg    |
|    6 | Jack | jek.jpg    |
|    9 | Joke | jok.jgp    |
+------+------+------------+

situation 1: 
I want to prefix all data in "images" column to look like "/path/to/img/file.jpg"

solution 1: 
UPDATE jos_data SET images = CONCAT('/path/to/img/', images) WHERE id IN (1,2,6,9);


situation 2:
I want to prefix all data in "images" column to look like "file.jpg--noext"

solution 2:
UPDATE jos_data SET images = CONCAT(images, '--noext') WHERE id IN (1,2,6,9);

done.

Extract URL from file in linux

sed ‘s/http/\^http/g’ *.mkv | tr -s “^” “\n” | grep http| sed ‘s/\ .*//g’
sed ‘s/http/\^http/g’ *.mkv | tr -s “^” “\n” | grep http| sed ‘s/\ .*//g’
sed ‘s/http/\^http/g’ *.mkv | tr -s “^” “</p>” | grep http| sed ‘s/\ .*//g’
sed ‘s/http/\^http/g’ *.mkv | tr -s “^” “<\p>” | grep http| sed ‘s/\ .*//g’
sed ‘s/http/\^http/g’ *.mkv | tr -s “^” “<\/p>” | grep http| sed ‘s/\ .*//g’
sed ‘s/http/\^http/g’ *.mkv | tr -s “^” “#” | grep http| sed ‘s/\ .*//g’
sed ‘s/http/\^http/g’ *.mkv | tr -s “^” “\n” | grep http| sed ‘s/\ .*//g’