DEV Community

Shaikh Al Amin
Shaikh Al Amin

Posted on • Edited on

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Check the current time in the database system

 

select now();
Enter fullscreen mode Exit fullscreen mode

Show table schema

show create table employees
Enter fullscreen mode Exit fullscreen mode

Turn off unique key check

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;
Enter fullscreen mode Exit fullscreen mode

Check the current timezone in the database

SELECT @@system_time_zone;
Enter fullscreen mode Exit fullscreen mode

Check foreign key check is enabled or not

SELECT @@FOREIGN_KEY_CHECKS
Enter fullscreen mode Exit fullscreen mode

This will set MySQL timezone in UTC

SET GLOBAL time_zone = '+00:00'; 
Enter fullscreen mode Exit fullscreen mode

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


 

SET @@session.time_zone = "+00:00";
Enter fullscreen mode Exit fullscreen mode

Foreign key check off

SET FOREIGN_KEY_CHECKS=0;
--your code here 
SET FOREIGN_KEY_CHECKS=1;
Enter fullscreen mode Exit fullscreen mode

Check database isolation level

SELECT @@transaction_isolation;
SELECT @@global.transaction_isolation;
Enter fullscreen mode Exit fullscreen mode

Turn off SQL Safe update

SET SQL_SAFE_UPDATES=0;
--UPDATE table name SET columnname=1;
SET SQL_SAFE_UPDATES=1;
Enter fullscreen mode Exit fullscreen mode
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;
Enter fullscreen mode Exit fullscreen mode

Check the default character set of a table

SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "employees_db";
Enter fullscreen mode Exit fullscreen mode
SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
Enter fullscreen mode Exit fullscreen mode

Create a database with character set and collation

create database employee_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay