mysql : Specified key was too long; max key length is 767 bytes

Set the following For key column.

• INNODB utf8 VARCHAR(255)
• INNODB utf8mb4 VARCHAR(191)

export mysql table to csv with headers

SELECT * FROM users
INTO OUTFILE ‘/results/result.txt’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;

Truncate all tables in a MySQL database in one command

Drop all tables

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done

Truncate  All tables

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done

mysql Remove duplicated keep latest

DELETE t1 FROM tablename t1
INNER JOIN tablename  t2
WHERE
t1.id< t2.idAND
t1.title = t2.title;

mysql update column value from another table

UPDATE TableB 
SET TableB.value = (
    SELECT TableA.value 
    FROM TableA
    WHERE TableA.name = TableB.name LIMIT 1
);

SQL , select last word in string

SELECT SUBSTRING_INDEX(TRIM(`lastname`), ‘ ‘, –1) FROM name

set last name in other column

UPDATE `names` set `surname` = SUBSTRING_INDEX(TRIM(`name`),’ ‘, -1)

Remove Last Word from string

UPDATE names SET fullname=RTRIM(REVERSE(SUBSTRING(REVERSE(fullname),LOCATE(" ",REVERSE(fullname)))))

import and export a MySQL database

Export

mysqldump -u username -p dbname > dbexport.sql

Import
mysql -u username -p dbname < dbexport.sql

sql Select arabic Columns

SELECT column_name FROM table_name WHERE NOT column_name RLIKE '[[:<:]][^\u0000-\u007F]+[[:>:]]'

Import CSV File Into MySQL Table

LOAD DATA LOCAL INFILE  ‘./table_name.csv’

INTO TABLE table_name

FIELDS TERMINATED BY ‘,’

ENCLOSED BY ‘”‘

LINES TERMINATED BY ‘\n’

IGNORE 1 ROWS;

allow root login in phpmyadmin

mysql -uroot -p[PASSWORD]
use mysql;
update user set plugin=” where User=’root’;
flush privileges;
\q