MySQL

Convert Date to String:

• DATE_FORMAT(NOW(), '%Y%m%d%H%i%s')   (Sample Output: 20221402153522) (YearMonthDayHourMinuteSecond)

How to find all tables with particular column name:

• SELECT table_name, column_name from information_schema.columns WHERE UPPER(column_name) LIKE '%FOO%' order by 1, 2;

Show MySQL Version via query:

• SHOW VARIABLES LIKE "%version%";

Using a variable at "Where" clause when the column is latin

If your column in "Where" clasue is latin and you set a variable like this in MySQL Workbench "SET @myVar= 'etc';" your query will run without index and result likely won't return. You have to CONVERT your variable to latin (because MySQL Workbench wil your string as UTF8):

• CONVERT(@myVar USING latin1) 

Concatenate two columns (sample)

SELECT CONCAT(column1, ' (' , column2, ')') FROM table;

Remove a character from the beginning and end of a column

TRIM(BOTH ',' FROM your_record_name)

Format decimal places

ROUND(2229.999, 2)

Using JSON_EXTRACT in MySQL Ver. > 5.7.8

Suppose you have a column which containing JSON data. You can get a value from that JSON tree without regex etc. Example query:

  SELECT json_extract(JSON_COLUMN, '$.taxRate') AS taxRate FROM transactions where id = 12345

Make a user's password NEVER EXPIRE

First query MYSQL for expire policy of that user: select user, password_lifetime from mysql.user where user ='myuser' and host = '%';

You will see password_lifetime as NULL. Than change policy for never expire: ALTER USER 'myuser'@'%' PASSWORD EXPIRE NEVER;

Than query again. You will see password_lifetime as 0.

Get all the tables sizes

SELECT TABLE_NAME, table_rows, data_length, index_length,

round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"

FROM information_schema.TABLES WHERE table_schema = "rum_schema";

Decimal to IP (for rum engine db)

INET_NTOA(case when clientIPv4 > 0 then clientIPv4 when clientIPv4 <0 then 4294967296+clientIPv4 else 0 end) as clientIPv4

From TimeStamp to Human DateTime (for rum engine db)

from_unixtime(timestamp/1000)

Date-Time Comparison

(NOW() - INTERVAL 1 HOUR)

Get First Row of a Group By

MySql already gives you an only one record when you use group by. But if you add ORDER BY to get the first row of your choice YOU CAN'T GET THE DESIRED RESULT. Because Group By is executed before Order By. So you have to do this:

    SELECT article, dealer, price

    FROM   shop s1

    WHERE  price=(SELECT MAX(s2.price)

                              FROM shop s2

                            WHERE s1.article = s2.article);

Select rows with Max Value grouped by two columns

select t1.* from attachments t1

left join attachments t2

on t1.entity_id = t2.entity_id and t1.group_id = t2.group_id and

   t1.version_number < t2.version_number

where t2.version_number is null

How to use GROUP BY to concatenate strings in MySQL?

SELECT id, GROUP_CONCAT(myColumn ORDER BY myTime DESC SEPARATOR ', ') FROM table GROUP BY id;

Create table from select result

Creata table new_table  select a,b from xyz;

Create temporary table from select result

CREATE TEMPORARY TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;

GROUP BY year and month?

GROUP BY YEAR(record_date), MONTH(record_date)

How to get bulk data using an SQL query in SSH command:

mysql -h xxx.xxx.xxx.xxx -u USERNAME -pPASSWORD < tempQuery.sql > out.csv (ATTENTION: Don't put space before PASSWORD)