How to Change MySQL Database Collation

Table of Contents

1. What is Database Collation?
2. Before You Start
3. Step 1: Access cPanel
4. Step 2: Open phpMyAdmin
5. Step 3: Select Your Database
6. Step 4: Change Database Collation
7. Step 5: Change Table Collations
8. Step 6: Test Your Website
9. Common Collation Types
10. Troubleshooting
11. Getting Help

What is Database Collation?

Simple Explanation

Database collation determines how your database sorts and compares text. It affects:

• How text is sorted (A-Z order)
• Whether searches are case-sensitive
• How special characters are handled
• Support for different languages

Why Change Collation?

• To support special characters (like é, ñ, ü)
• To fix sorting issues with names or text
• To support different languages properly
• To match your website's language requirements

Warning

Always backup your database before making changes! Changing collation can affect how your data is displayed and sorted.

Before You Start

What You'll Need

• Access to your cPanel account
• Your database name
• About 10-20 minutes
• Important: A backup of your database

Create a Backup First

1. Go to cPanel ? Databases ? phpMyAdmin
2. Select your database
3. Click "Export" at the top
4. Click "Go" to download your backup
5. Save this file somewhere safe!

Step 1: Access cPanel

Method 1: Through iFastNet Client Portal

1. Go to https://ifastnet.com/portal/clientarea.php
2. Log in with your iFastNet account details
3. Find your hosting service
4. Click "Login to cPanel"

Method 2: Direct Access

1. Go to https://yourdomain.com/cpanel (replace "yourdomain.com" with your domain)
2. Enter your cPanel username and password
3. Click "Log in"

Step 2: Open phpMyAdmin

1. In cPanel, scroll down to the "Databases" section
2. Click on "phpMyAdmin"
3. phpMyAdmin will open in a new window/tab
4. You'll see a list of your databases on the left side

Step 3: Select Your Database

1. Look at the list of databases on the left side
2. Click on the database you want to change
3. The database will expand to show its tables
4. The main area will show information about your database

Step 4: Change Database Collation

1. With your database selected, click on the "Operations" tab at the top
2. Scroll down to find the "Collation" section
3. You'll see a dropdown menu with the current collation
4. Click the dropdown to see available collations
5. Choose your new collation (see Common Collation Types below)
6. Click "Go" to apply the change
7. You should see a success message

Most Common Choices

• utf8mb4_unicode_ci - Best for international websites
• utf8mb4_general_ci - Good general purpose option
• latin1_swedish_ci - For English-only websites

Step 5: Change Table Collations

Important: Changing the database collation doesn't automatically change existing tables. You need to change each table separately.

For Each Table in Your Database:

1. Click on a table name from the list on the left
2. Click the "Operations" tab at the top
3. Scroll down to the "Table options" section
4. Find the "Collation" dropdown
5. Select the same collation you chose for the database
6. Click "Go"
7. Repeat this for every table in your database

Quick Method for All Tables:

1. Select your database from the left sidebar
2. Click "Check All" at the bottom of the table list
3. From the "With selected:" dropdown, choose "Change table collation"
4. Select your new collation
5. Click "Go"

Step 6: Test Your Website

1. Visit your website in a web browser
2. Check that text displays correctly
3. Test any search functions
4. Look for any strange characters or formatting issues
5. Test forms and user input features

Things to Check

• Special characters display correctly (é, ñ, ü, etc.)
• Sorting works properly (A-Z order)
• Search functions work as expected
• No strange symbols or question marks in text

Common Collation Types

For International/Multi-language Websites

• utf8mb4_unicode_ci - Best choice for most websites
• utf8mb4_general_ci - Faster but less accurate sorting

For English-only Websites

• latin1_swedish_ci - Traditional choice for English
• utf8mb4_unicode_ci - Still recommended even for English

For Specific Languages

• utf8mb4_bin - Case-sensitive, exact matching
• utf8mb4_unicode_520_ci - Latest Unicode standard

What the Endings Mean

• ci = Case Insensitive (A = a)
• cs = Case Sensitive (A ? a)
• bin = Binary (exact byte comparison)

Troubleshooting

My website shows strange characters

Problem: Text shows as question marks or weird symbols Solution:

1. Make sure you chose a UTF8 collation (utf8mb4_unicode_ci)
2. Check that your website files are also saved in UTF-8 format
3. You may need to re-enter affected text

Sorting is not working correctly

Problem: Names or lists don't sort in alphabetical order Solution:

1. Make sure all tables have the same collation
2. Try utf8mb4_unicode_ci for better sorting
3. Check that your application isn't overriding the sort order

I can't see the Operations tab

Problem: The Operations tab is missing or grayed out Solution:

1. Make sure you selected a database or table first
2. Try refreshing phpMyAdmin
3. Check that you have proper permissions

Changes didn't take effect

Problem: The collation shows as changed but text still looks wrong Solution:

1. Make sure you changed BOTH database AND table collations
2. Clear your browser cache
3. You may need to re-import your data

Getting Help

iFastNet Support

If you need help or something goes wrong:

1. Support Portal: Go to https://support.ifastnet.com/login.php
2. First time? Click register to create a support account
3. Existing users: Log in with your support credentials
4. Create a new support ticket

Alternative Support Access

You can also access support through your client portal at https://ifastnet.com/portal/clientarea.php

What to Include in Your Support Request

• Your domain name
• The database name you're working with
• What collation you're trying to change to
• Any error messages you're seeing
• Screenshots of the problem (if possible)

Before Contacting Support

1. Make sure you have a backup of your database
2. Try refreshing phpMyAdmin and trying again
3. Check that you followed all steps correctly
4. Note down any error messages exactly as they appear

Important Reminders

Always Backup First

• Export your database before making any changes
• Save the backup file in a safe location
• Test that you can restore from the backup if needed

Be Patient

• Collation changes can take time on large databases
• Don't close phpMyAdmin while changes are processing
• Wait for success messages before moving to the next step

Test Everything

• Check your website thoroughly after making changes
• Test all features that involve text or searching
• Look for any display problems with special characters

Remember: If something goes wrong, you can always restore your database from the backup you created before starting!