[MySql] FIND_IN_SET() function

Objectif: update multiple rows in mysql

Quick Solution (for example):

UPDATE my_table SET my_field=first_value WHERE id=1;
UPDATE my_table SET my_field=second_value WHERE id=2;
UPDATE my_table SET my_field=third_value WHERE id=3;
...

Problem : performance

Other solution with function FIND_IN_SET() :

UPDATE `my_table`
SET my_field = find_in_set(id, 'first_value, second_value, third_value, ...')
WHERE id IN (1, 2, 3, 4, ...)

Find more explaination :
http://www.w3resource.com/mysql/string-functions/mysql-find_in_set-function.php

[MySQL] mysql date functions

Mysql date functions :

SELECT NOW(),CURDATE(),CURTIME()

Result :

NOW()	CURDATE()	CURTIME()
2016-05-03 09:58:20	2016-05-03	09:58:20

Yeah, it is the time when I execute the command.

To be honest, the ressource is from w3schools, a very good website to start dev.

[MySQL] mysql command export only CREATE TABLE

To export the mysql table creation info without extracting all the data :

Dump option :

1. no-data – export without data

 

Example :

 mysqldump -u your_login -pyour_password --no-data your_database > /your_file_export.sql

[OpenShift] Deploy PHP/MySQL in OpenShift got 503 Service Temporarily Unavailable / 504 Gateway Time-out

Context :

The objective is to deploy the connection of MySQL with PHP in an OpenShift Server.
OpenShift is PaaS provided by Red Hat. For more info, read wikipedia OpenShift.

Problem :

The server returns :

503 Service Temporarily Unavailable

or

504 Gateway Time-out

Mysql — multiple columns index vs multiple indexs

A good blog for mysql performance :

It compares between multiple columns index and multiple indexs with 5 different query.

http://www.mysqlperformanceblog.com/2008/08/22/multiple-column-index-vs-multiple-indexes/

Mysql useful commands

### Mysql dump ###

== Syntax global == 

mysqldump [options] db_name [tables]

== Syntax to backup only one database == 

mysqldump -u [username]  -p [password] [database_name] > [backup_file.sql]

== Syntax to backup multiple databases ==

mysqldump -u [username]  -p [password] –databases [database_names] > [backup_file.sql]

== Syntax to restore databases ==

mysqldump -u [username]  -p [password] [database_to_restore] < [backup_file.sql]

ex. [Only one database]

mysqldump -uroot -ppassword database_name > file_name

ex. [Multiple databases]

mysqldump -uroot -ppassword –databases database_name1 database_name2 > file_name

 

MySQL – Modify table name by command line

Phpmyadmin is a very good graphic interface to operate data in the database. But have you ever met the case that you want to modify the name of your table, but you don’t want to delete the whole data in the table and recreate a new table and insert data and …