How to fix ERROR 1060: Duplicate column name in MySQL

-- Example: This CREATE TABLE will fail
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255),
  email VARCHAR(100)  -- DUPLICATE!
);
-- ERROR 1060: Duplicate column name 'email'

DESCRIBE users;
SHOW CREATE TABLE users\G

-- View all columns in the table
SHOW COLUMNS FROM users;

-- Or use information schema
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'users' AND TABLE_SCHEMA = 'your_database';

ALTER TABLE users DROP COLUMN old_column_name;

-- WRONG: Duplicate email column
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255),
  email VARCHAR(100)
);

-- CORRECT: Rename the second one or remove it
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255),
  email_backup VARCHAR(100)  -- Renamed to be unique
);

-- WRONG: Both tables have 'name' column, creates duplicate
CREATE VIEW user_profile AS
SELECT users.name, profiles.name
FROM users
JOIN profiles ON users.id = profiles.user_id;
-- ERROR 1060: Duplicate column name 'name'

-- CORRECT: Use aliases to distinguish columns
CREATE VIEW user_profile AS
SELECT users.name AS user_name, profiles.name AS profile_name
FROM users
JOIN profiles ON users.id = profiles.user_id;

-- MySQL 8.0+: Safely add column only if it doesn't exist
ALTER TABLE users ADD COLUMN last_login TIMESTAMP NULL IF NOT EXISTS;

-- Check if column exists before adding
ALTER TABLE users 
ADD COLUMN last_login TIMESTAMP NULL;
-- Will fail if it already exists; wrap in application logic to skip if needed

-- Verify no duplicates
DESCRIBE users;

-- Re-run your original statement
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Or re-run your ALTER
ALTER TABLE users ADD COLUMN phone VARCHAR(20);