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;
Top comments (0)