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...
We will create a bash script and run via cron job and set the time as per your requirement. If you don't know how to create bash script, please read "How to create Linux bash script" #!/bin/bash # Shell script to restart MySQL server if it is killed or not working # due...
Replace field data with another value UPDATE table_name SET field = replace(field,'string-to-find','string-that-will-replace-it');
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;
MySQL allows the following regular expression metacharacters: . match any character ? match zero or one * match zero or more + match one or more {n} match n times {m,n} match m through n times {n,} match n or more times ^ beginning of line $ end of line [[:<:]] match beginning of words [[:>:]] match ending of...
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...
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
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)...
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...
The provided SQL commands perform a search query on a table named "vehicles" to find entries that match specific criteria. Let's break down each command and its purpose: SELECT command: select concat(`make`, ' ', `model`, ' ', `style`, ' ', `transmission`, ' ', `exterior_color`) as v, match (`make`, `model`, `style`, `transmission`, `exterior_color`, `vehicle_features`, `body_style`) against ('BMW...
