fbpx

MySql

Copy MySql specific table rows to another table

The safest way to do it is to fully specify the columns both for insertion and extraction.  You can copy the schema of the table by this command. CREATE TABLE new_table LIKE old_table; You can disable index building in start and enable indexes in end. This will speed up the process in...

Read more...

MySql handy commands

Replace field data with another value UPDATE table_name SET field = replace(field,'string-to-find','string-that-will-replace-it');    

Read more...

MySql Database Size

This query will get mysql database size in mb. Replace the DATABASE_NAME with actual database name in query below   SELECT CONCAT( sum( ROUND( ( ( database_info.data_lenght + database_info.index_lenght - database_info.data_free_lenght ) / 1024 / 1024 ), 2 ) ), " MB" ) AS Size FROM ( SELECT sum( CAST(DATA_LENGTH AS UNSIGNED) ) AS data_lenght, sum( CAST(INDEX_LENGTH AS UNSIGNED) ) AS index_lenght, sum(CAST(DATA_FREE AS UNSIGNED)) AS data_free_lenght FROM INFORMATION_SCHEMA. TABLES WHERE TABLE_SCHEMA LIKE '%DATABASE_NAME%' ) AS database_info;

Read more...

partition a mysql table with number of rows

Specify the table name where <TABLE_NAME> is given and specify the column name by which you want to do partition. 10, 20, 30, 40 are the numbers  by which the partition would be created. ALTER TABLE <TABLE_NAME> PARTITION BY RANGE(id) ( PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION...

Read more...

show mysql rows in one column

Using MySQL, I can list the category but want rows to transpose in single column comma separated. select category_name from categories Result: Rock POP Jazz Desired Result: Rock, POP,Jazz Solution: You can achive this by using group_concat function in mysql. select group_concat(category_name separator ', ') from categories;   Reference: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Read more...

Capitalize the first letter of every word in MySql query

A simple MySQL function to capitalize the first letter of every word in a string. CREATE FUNCTION CAP_FIRST (input VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC BEGIN DECLARE len INT; DECLARE i INT; SET len = CHAR_LENGTH(input); SET input = LOWER(input); SET i = 0; WHILE (i < len) DO IF (MID(input,i,1) = ' ' OR i = 0) THEN IF (i < len)...

Read more...

How to bulk update mysql quries

This is a very short and handy mysql query to update all records. There was a small sql injection in my database and i replaced the specific code from the "tag" and updated all the records. UPDATE tags SET keywords = REPLACE( keywords, " , ABC.com", ""  ) There was another issue...

Read more...

Mysql free text search

select concat(`make`, ' ', `model`, ' ', `style`, ' ', `transmission`, ' ', `exterior_color`) as v, match (`make`, `model`, `style`, `transmission`, `exterior_color`, `vehicle_features`, `body_style`) against ('BMW Black' IN BOOLEAN MODE) as r from vehicles where match (`make`, `model`, `style`, `transmission`, `exterior_color`, `vehicle_features`, `body_style`) against ('BMW Black' IN BOOLEAN MODE)   You would have to add fulltext index to make this working. ALTER...

Read more...