Monthly Archives: November 2012

MySQL’s SHOW CREATE TABLE for Oracle

Ever wondered, how to see create table code in Oracle?
as available in MySQL “show create table”

Here is how to do it

SQL> set pages 0
SQL> set long 999999
SQL> select dbms_metadata.get_ddl(‘TABLE’, ‘EMPLOYEES’, ‘EMP’) from dual;

MySQL: dropping all tables of a database with ease

So there is a need to delete all tables from database?

Possibilities:

  1. Drop database and recreate, what if you don’t want to drop database, could be many reasons.
  2. Fire drop table [tbl_name ...] command on MySQL prompt but then who is going to type all tables names.

Solution:
Run following command from command line

mysqldump -u [USERNAME] -p[PASSWORD] –add-drop-table –no-data [DATABASE] | grep ^DROP | mysql -u [USERNAME] -p[PASSWORD] -D [DATABASE]

Explained:

  1. First one takes dump of all tables of database without any data along with drop table command.
  2. Second part fetches lines starting with Drop from the output of first command.
  3. Third command executes queries fetched by second command which are only drop table commands.

MySQL: Load timezones info in MySQL Server

Run following command on command line:

mysql_tzinfo_to_sql  /usr/share/zoneinfo | mysql -u root -p mysql

and to change timezone of the mysql server, add following line in the my.cnf file (/etc/my.cnf)

default_time_zone=Asia/Kolkata

or run following query on MySQL prompt (this will be valid for current session only)

SET time_zone = ‘Asia/Kolkata’;

Run PHP scripts in background on Linux

To run a PHP script in background from command line and keep it running even if you exit the terminal, use following syntax:

nohup php script.php < /dev/null &

Explained:
nohup‘ is a POSIX command to ignore the HUP (hangup) signal, enabling the command to keep running after the user who issues the command has logged out.

In Unix-like operating systems, ‘/dev/null‘ or the null device is a special file that discards all data written to it.

‘&’ is to send the process in the background and return the command line prompt.

Common MySQL Data Types

Text Data Types
CHAR( ) fixed from 0 to 255 characters long
VARCHAR( ) variable from 0 to 255 characters long
TINYTEXT maximum length of 255 characters
TEXT maximum length of 65535 characters
BLOB maximum length of 65535 characters
MEDIUMTEXT maximum length of 16777215 characters
MEDIUMBLOB maximum length of 16777215 characters
LONGTEXT maximum length of 4294967295 characters
LONGBLOB maximum length of 4294967295 characters

Numeric Data Types
TINYINT( ) -128 to 127 SIGNED or 0 to 255 UNSIGNED
SMALLINT( ) -32768 to 32767 SIGNED or 0 to 65535 UNSIGNED
MEDIUMINT( ) -8388608 to 8388607 SIGNED or 0 to 16777215 UNSIGNED
INT( ) -2147483648 to 2147483647 SIGNED or 0 to 4294967295 UNSIGNED
BIGINT( ) -9223372036854775808 to 9223372036854775807 SIGNED or 0 to 18446744073709551615 UNSIGNED
FLOAT small number with a floating decimal point
DOUBLE( , ) large number with a floating decimal point
DECIMAL( , ) DOUBLE stored as a string , allowing for a fixed decimal point

Date Data Types
DATE YYYY-MM-DD
DATETIME YYYY-MM-DD HH:MM:SS
TIMESTAMP YYYYMMDDHHMMSS
TIME HH:MM:SS

 Scroll to top