MySql Type Casting

Some times in SQL query you need to change the data type of table field with keeping the table structure as it is. Some thing like Integer to string, string to integer, string to date etc…. This type of data type conversion is known as data type casting. For this operation in SQL you can use mysql CAST function.

The CAST() function takes a value of one type and produce a value of another type.

 

What is Type Custing?

Converting an expression of a given type into another type is known as type- casting.

 

Mysql CAST() :

Suppose in your database you have a table with this structure

 

Tablename : “TestTable”

Field	Type	Null
id	int(11)	No
name	varchar(100)	No
ordernumber	varchar(100)	No

 

And in this table you have some of value like this…

SQL query: SELECT * FROM `TestTable` LIMIT 0, 30 ;

id	name	ordernumber
1	Jewel	404
2	Ahmed	420
3	Shabuj	001
4	Rose	First
5	Janina	100
6	HaJani	112
7	Test	Start
8	NameValue	121
9	String	021
10	Rahim	1
11	Korim	4

When you try for a Recordset with ordernumber ASC Then tha result will shown like this….

SQL query: SELECT * FROM `TestTable` ORDER BY `ordernumber` ASC LIMIT 0, 30 ;

id	name	ordernumber
3	Shabuj	001
9	String	021
10	Rahim	1
5	Janina	100
6	HaJani	112
8	NameValue	121
11	Korim	4
1	Jewel	404
2	Ahmed	420
4	Rose	First
7	Test	Start

In this RecordSet when the Mysql query are executed it returned a sorted RecordSet with ordernumber ascending. But you want a sorted RecordSet with proper numerical order of ordername field. For which you can find a RecordSet as ..

 

id	name	ordernumber
4	Rose	First
7	Test	Start
10	Rahim	1
3	Shabuj	001
11	Korim	4
9	String	021
5	Janina	100
6	HaJani	112
8	NameValue	121
1	Jewel	404
2	Ahmed	420

 

You have to write this sql

SQL query: SELECT * FROM `TestTable` ORDER BY CAST(ordernumber AS UNSIGNED INTEGER ) ASC LIMIT 0 , 30 ;

Mysql CAST() Syntex :

CAST(expression as type)

The type can be one of the following values:

• BINARY[(N)]
• CHAR[(N)]
• DATE
• DATETIME
• DECIMAL[(M[,D])]
• SIGNED [INTEGER]
• TIME
• UNSIGNED [INTEGER]

More Example :

If a field with datatype VARCHAR(100) and also have a value “0420″

For normal selection of data you got a value 0420 which is a string. But you want a integer value that is 420.

You can write a sql like 

SQL query: SELECT CAST(fld_name AS UNSIGNED INTEGER) as

fld_name FROM tbl_name LIMIT 0,1;

Resulted Record will : 420 not 0420
For more details you can visit :

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast

What is Database Index in MySql?

If in a Database Table there are no index exist like primary or unique key then when you write a query for search anything from this table of course the search time cost will higher then when you use index in this table.

Cause in without index table the search perform must in entire table if in this table huge amount of record exist then you may find it is slow cause entire table searching.

Where if you use column index then searching cost will minimize from previous time cost. Cause when you use index it it will perform the search operation only on indexed row.

Indexed default row

1) primary key field

2) unique key field

Use of Index :

If you use this kinds of query then it is by default search first

$sql = “select *from table where primary_key_id = 40015″;

or

$sql = “select *from table where unique_key_name = ‘Jewel’”;

You can also add another indexed column for searching first

Alter a column which are indexed

$sql = “ALTER TABLE table ADD INDEX (‘lastname’) “;

in this query lastname field are indexed altered.

Now you can write sql for first search like this

$sql=”select * from table where lastname=’Ahmed’”;

this query will faster. Because The use of index.

Reference Site LInk : http://www.databasejournal.com/features/mysql/article.php/1382791

How to index practical image draw are given below Just follow the image.