MySQL - Membuat procedure untuk kompare table

Pernah aku mampir ke blog seseorang tp aku lupa alamatnya, dia membuat sebuah script procedure untuk melakukan compare ke beberapa table yang ada di MySQL. Berikut procedurenya:


USE mysql;
/* ----------------------------------
   Procedure structure for xp_execute
   ---------------------------------- */

DROP PROCEDURE IF EXISTS `xp_execute`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `xp_execute`(vqueryddl longtext)
begin
 set @ssql = vqueryddl;
 prepare query from @ssql;
 execute query;
end;;
DELIMITER ;

/* -------------------------------------------
-- Procedure structure for xp_routines_compare
   ------------------------------------------- */
DROP PROCEDURE IF EXISTS `xp_routines_compare`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `xp_routines_compare`(v_namadatabase1 varchar(255), v_namadatabase2 varchar(255))
BEGIN
DROP VIEW IF EXISTS __db1;
SET @sql = CONCAT('
CREATE VIEW __db1 AS
select
 r.`db` as nama_database,
 r.`type` as tipe_routine,
 r.`name` as nama_routine,
 r.`param_list` as parameter_routine,
 r.`body` as isi_routine,
 r.`returns` as nilai_balik
from mysql.proc as r
where
 r.db = "',v_namadatabase1,'"
with check option');
call xp_execute(@sql);

DROP VIEW IF EXISTS __db2;
SET @sql = CONCAT('
CREATE VIEW __db2 AS
select
 r.`db` as nama_database,
 r.`type` as tipe_routine,
 r.`name` as nama_routine,
 r.`param_list` as parameter_routine,
 r.`body` as isi_routine,
 r.`returns` as nilai_balik
from mysql.proc as r
where
 r.db = "',v_namadatabase2,'"
with check option');
call xp_execute(@sql);

select db1.nama_routine as routine_yg_berbeda,
 TRIM(CONCAT
   (  IF(strcmp(LCASE(REPLACE(REPLACE(db1.parameter_routine,CHAR(32),''),CHAR(13),'')
),LCASE(REPLACE(REPLACE(db2.parameter_routine,CHAR(32),''),CHAR(13),'')))
<> 0,'PARAMETER',''),' ',
      IF(strcmp(LCASE(REPLACE(REPLACE(db1.isi_routine,CHAR(32),''),CHAR(13),'')),
LCASE(REPLACE(REPLACE(db2.isi_routine,CHAR(32),''),CHAR(13),''))) <> 0,'BODY',''),' ',
      IF(strcmp(LCASE(REPLACE(REPLACE(db1.nilai_balik,CHAR(32),''),CHAR(13),'')),
LCASE(REPLACE(REPLACE(db2.nilai_balik,CHAR(32),''),CHAR(13),''))) <> 0,'RETURN',''),' '
   )) as letak_perbedaan
from __db1 as db1 JOIN __db2 as db2
where db1.nama_routine = db2.nama_routine and
  (strcmp(LCASE(REPLACE(REPLACE(db1.parameter_routine,CHAR(32),''),CHAR(13),'')),
LCASE(REPLACE(REPLACE(db2.parameter_routine,CHAR(32),''),CHAR(13),''))) <> 0
   or strcmp(LCASE(REPLACE(REPLACE(db1.isi_routine,CHAR(32),''),CHAR(13),'')),
LCASE(REPLACE(REPLACE(db2.isi_routine,CHAR(32),''),CHAR(13),''))) <> 0
   or strcmp(LCASE(REPLACE(REPLACE(db1.nilai_balik,CHAR(32),''),CHAR(13),'')),
LCASE(REPLACE(REPLACE(db2.nilai_balik,CHAR(32),''),CHAR(13),''))) <> 0  )
UNION ALL
select db1.nama_routine as routine_yg_berbeda, CONCAT('routines baru di db : ',v_namadatabase1) as letak_perbedaan
from __db1 as db1
where db1.nama_routine not in (select db2.nama_routine from __db2 as db2)
UNION ALL
select db2.nama_routine as routine_yg_berbeda, CONCAT('routines baru di db : ',v_namadatabase2) as letak_perbedaan
from __db2 as db2
where db2.nama_routine not in (select db1.nama_routine from __db1 as db1)
ORDER BY routine_yg_berbeda ASC;

DROP VIEW IF EXISTS __db1;
DROP VIEW IF EXISTS __db2;

END;;
DELIMITER ;

MySQL - Membuat script bat untuk proses dump database MySQL

set @path_backup = 'D:\\backup\\';
set @sv_host = 'localhost';
set @sv_port = '3306';
set @sv_uname = 'root';
set @sv_pwd = 'upie';

select * into outfile 'D:\\script_batch_mysqldump.bat'
lines terminated by '\n'
from
(
select concat('mysqldump --host=',@sv_host,' --port=',@sv_port,' -u',@sv_uname,' -p',@sv_pwd,' --add-drop-table --max_allowed_packet=1GB --routines --triggers --quote-names -E --no-data ', db.schema_name ,' > ',@path_backup, 'structure_',db.schema_name,'.sql') as syntax from information_schema.schemata as db where db.schema_name not in ('information_schema','mysql','test')

union

select concat('mysqldump --host=',@sv_host,' --port=',@sv_port,' -u',@sv_uname,' -p',@sv_pwd,' --add-drop-table --extended-insert --hex-blob --max_allowed_packet=16GB --lock-tables --no-create-db --no-create-info --quote-names --order-by-primary --dump-date ',db.schema_name,' > ',@path_backup, 'data_', db.schema_name,'.sql') as syntax from information_schema.schemata as db where db.schema_name not in ('information_schema','mysql','test')
) as script;

MySQL - Delete table menggunakan inner join

DELETE a
from t_gaji as a inner join t_pegawai as b on a.nik=b.nik
WHERE a.periode='201206' and b.departemen='OUTSOURCING' and b.penempatan_kerja='BALARAJA';

MySQL - Merubah nama hari dari format eng ke dalam format indonesia

Select DATE_FORMAT(tanggal, "%W") AS `Nama_Hari_Eng`
        , CASE DAYOFWEEK(tanggal)
                WHEN 1 THEN 'Minggu'
                WHEN 2 THEN 'Senin'
                WHEN 3 THEN 'Selasa'
                WHEN 4 THEN 'Rabu'
                WHEN 5 THEN 'Kamis'
                WHEN 6 THEN 'Jumat'
                WHEN 7 THEN 'Sabtu'
          END AS `Nama_Hari_ID`
from t_transaksi;