MySQL useful commands

Find Duplicate In table

Not functional fully in some cases

SELECT full_name, count(full_name) FROM brollops_old_and_new_vendors.users group by full_name having count(full_name) > 1;

Looks functional [little slow response]

SELECT * FROM brollops_old_and_new_vendors.users AS `outer` WHERE (SELECT COUNT(*) FROM brollops_old_and_new_vendors.users AS `inner` WHERE `inner`.full_name = `outer`.full_name) > 1

Check the current time in the database system

 

select now();

Show table schema

show create table employees

Turn off unique key check

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

Check the current timezone in the database

SELECT @@system_time_zone;

Check foreign key check is enabled or not

SELECT @@FOREIGN_KEY_CHECKS

This will set MySQL timezone in UTC

SET GLOBAL time_zone = '+00:00'; 

By this you can change the timezone only for your particular session

 

SET @@session.time_zone = "+00:00";

Foreign key check off

SET FOREIGN_KEY_CHECKS=0;
--your code here 
SET FOREIGN_KEY_CHECKS=1;

Check database isolation level

SELECT @@transaction_isolation;
SELECT @@global.transaction_isolation;

Turn off SQL Safe update

SET SQL_SAFE_UPDATES=0;
--UPDATE table name SET columnname=1;
SET SQL_SAFE_UPDATES=1;

SET SQL_SAFE_UPDATES = 0;
UPDATE buildings_zenrins_rename AS bm_back
INNER JOIN sales_histories_new AS sales_new ON sales.id = sales_new.id
SET sales.comments = sales_new.comments;
SET SQL_SAFE_UPDATES=1;

Check the default character set of a table

SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "employees_db";

SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;

Create a database with character set and collation

create database employee_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Check query performance

-- FLUSH STATUS;
-- SELECT dept.name,emp.* FROM departments as dept left join employees as emp on dept.id = emp.dept_id where emp.dept_id is null;
-- SHOW STATUS;