Mysql Copy table structure only

Sometimes we need to create a empty table from already existing table , here is the command to create new table from already existing table.

CREATE TABLE 'newtable' LIKE 'oldtable'

Here you can create a 'newtable' with the same structure from 'oldtable'. No one data will copied.

Note: Autoincrement also reset to 1.

Copy table query

For some requirements like backup, temp table creation we need to copy a table. In mysql we have the option " CREATE TABLE " to create a new table we can use this also for copy table.

Copy structure only:
Some time we only need the copy the table without data. here is query for that.

CREATE TABLE dest_table_name LIKE source_table_name;

This will create a new table like source table but without data.

Copy structure and data:
we can copy both data and structure via single query

CREATE TABLE dest_table_name SELECT * FROM source_table_name;

This create a new table with both data and structure.

Copy Data only:
if you have a table like another table , just need to copy the data only mean just use the "insert into" in mysql.

INSERT INTO dest_table_name select * from source_table_name;

Find and replace in mysql

For find and replace a text in a field is very easy in mysql. we can replace it with single query.
here is the find and replace query in mysql.


UPDATE your_tablename SET your_fieldname = REPLACE(your_fieldname,'your_search_string','your_replace_string');


using the above query you can easily replace the search string .
 The main drawback is it's case sensitive.


(ex)

UPDATE jos_content SET `fulltext` = replace( `fulltext` , 'Replace', 'test' ) WHERE id =1

using this find and replace query you just replace the string  'Replace' first letter caps lock. it will not replace "REPLACE", "replace" etc..

How to export mysql database?

There are two ways to export mysql database from your source.

1. phpMyAdmin
2. Command prompt

Export Mysql Database:

     First thing was how to export a database using phpMyAdmin.

 *) Log into phpMyAdmin.

*) select your database from left side database list

*)  Click the Export tab at the top

*)  select the all/required table on right side column and if you want to download as file mean just select the file name and check save as file option in footer.

*) if you only structure of the tables just uncheck the checkbox near data header or if you need the data only just uncheck the checkbox near structure header. select required options options underneath the structure and data header.

*) then click GO button and your database will exported.


if you using phpmyadmin it will take some time and if database have too  much content
(largesize) mean , butter you can use command prompt to export the db, using command prompt we can easily download large database with in few mins

first open mysql command prompt
*) in windows
     if you using wampp just click the icon in taskbar , and select mysql-> mysql console.

*) in unix/ubuntu
     just open the terminal and go to mysql path

Run this command:

mysqldump -h host-u root -p database>destination_path/somename.sql

Technical tags :

export mysql database, export mysql table, mysql command , export database,mysqldump tutorial, phpmyadmin.

i will explain about import database upcoming post

Mysql related books:

Full text indexing in mysql

All of you know searching option to site was one of the main big hand to viewers to stay on our site.hope all as know the success of google, yes the main success of google was search algorithm.but for a single site we don't want to create that kind of big search algorithm development , MySQL give one of the best option to search the content.

That was indexing, indexing used for speed up the query result.here i am going to explain full text indexing.
MySQL support FULLTEXT indexing from version 3.23.2.using full text intexing we acan able to give best search result with in our site.


NOTE :Full text index only support with MyISAM tables, and the columns should only with CHAR,VARCHAR or TEXT.


How to add full text index to MySQL table:

   we have three was to add Fulltext index in table, before that we must need to check where the table engine in 'MyISAM' type. if it not we are not able to add fulltext indexing.

Then how to change table engine, if you have an idea no problem , if not please click here.

adding full text indexing to column just use this on

ALTER TABLE tablename ADD FULLTEXT(colum name);

   * more than one column just use comma separated in column name.

once full text index created , we can able to search MATCH and AGAINST statement and it will use to in
dex based serach result.

Couple of mainthings to know about full text indexing:
------------------------------------------------------

*) Search word minimum length is 4 characters and case sensitive
*)  it give delayed to load data into a table
            insert query works fine but update it take some time.

Alter Mysql table engine

MySQL support different types of storage engines. They are separated by two types of storage engiens based on table handling
 1) transactional tables            
 2) nontransactional tables


NOTE: MySQl allow differnt storage engine types of tables with in a database.
Here we going to see the options how we add the engine detail to table

*) create table
       we can able to define which engine type going to use the table, using ENGINE or TYPE table option.Default engine types to table was MyISAM.

CREATE TABLE tablename  (i INT) ENGINE = MYISAM;
CREATE TABLE tablename  (i INT) TYPE = MYISAM;

*) Alter table

if you want to change the storage engine mean, 

just use alter table query

ALTER TABLE tablename   ENGINE = MYISAM;
ALTER TABLE tablename   TYPE = MYISAM;

*) dynamic use

MySQL allow us change the table engine during 

the current session.using "SET" option we can 

able to change the storage engine to the 

particular session

SET storage_engine=MYISAM;
SET table_type=MYISAM;

Change mysql engine for table?

In this post i will explain how to change the mysql engine for table.

Two ease ways to we can change the mysql table engine:

with help of query
------------------
*) ALTER TABLE your_table_name ENGINE = your_engine;

using this query we can easily change the mysql table engine.

Reset mysql table autoincrement?

In mysql you can easily reset auto increment value in a table using single query.

ALTER TABLE tablename AUTO_INCREMENT = 1;

it will reset the auto increment based on existing records in that table

*) if table have existing records the auto increment value will reset to one higer than the maximum record (max value + 1) .

*) if  table have no records then it will automatically set as 1.

if you want to reset the auto increment value as 1 but need to maintain the records as such mean mysql don't allow it. example if you have records from 5,6,7 etc, you want to reset the auto increment to 1 then mysql won't allow it , it will only set 8 as auto increment value.


Technical Tags:
     AUTO_INCREMENT, auto increment , reset mysql auto increment,autoincrement reset.

how to automatically get line number using mysql query?

we are all easily find the number of records will come using myslq query.now i am going to explain about how to automatically add the line number in mysql query.

in mysql we have the option to set the user defined variables, using that we are going to add automatic line number in mysql result.

more information about mysql user variable from here

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

using that user defined variable we can add get automatically line number.

example:
select @linenum:=@linenum+1 row,a.title from (select @linenum:=0) l ,jos_content a

(select @linenum:=0) l using this we can set user defined variable as 0.using the above query image will come like this