Magento change database query collation

Magento change database query collation

October 5, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento change database query collation

Solving Collation Issues in Magento for Swedish Characters

In Magento, when dealing with multilingual sites that include special characters like those found in the Swedish alphabet (Ä, Ö,), you might encounter sorting issues if the database collation is not properly configured. This is because the default utf8_general_ci collation used by most Magento installations does not handle these specific characters correctly.

Identifying the Collation Problem

The issue manifests when sorting products by name in your catalog. Instead of being sorted alphabetically, words starting with special Swedish letters are grouped together and appear out of place in the list:

A

Ä
B
...

This happens because the utf8_general_ci collation is case-insensitive, which causes characters like Ä to be treated as A when sorting.

Understanding Collations in MySQL

Before diving into the solution, it’s important to understand how collations work in MySQL. A collation defines the rules for sorting and comparing strings. For Swedish characters to sort correctly, we need a collation that is case-sensitive and accent-insensitive, but still recognizes the special characters. The utf8_swedish_ci collation fits this description.

Overriding the Collation at Query Level

You mentioned finding ways to override the collation used in queries, which is a good starting point. However, doing this globally across all Magento queries isn’t straightforward as it would require modifying core files and could potentially break updates or other functionality.

Instead, let me suggest a targeted approach that focuses on the specific tables where the sorting issue occurs—namely, the catalog_product_entity table in your case.

Changing Collation for Specific Tables

To change the collation of this table specifically, you can use the following SQL query:

ALTER TABLE catalog_product_entity CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8_swedish_ci;

This command converts the table to use the utf8mb4 character set (which supports a wider range of characters) and sets the collation to utf8_swedish_ci. Run this query in your MySQL client or execute it from a custom script.

Ensuring Database Consistency

After running the command, you need to ensure that all existing data is consistent with the new collation. For this, Magento provides a built-in feature called the “Use Indexes” option during reindexing, which ensures that sorting indexes are rebuilt based on the current collation settings.

Reindexing for Sorting Updates

To trigger this process:

  1. Log in to your Magento admin panel.
  2. Navigate to System > Index Management.
  3. In the Reindex Required section, look for any actions with a status of “Reindex required.”
  4. Click on the “Reindex All” button.

This will update all sorting indexes based on the new collation settings. If you want more granular control, you can select and reindex specific actions like “Catalog URL Rewrite” or “Catalog Category Flat Data.”

Conclusion

Changing the collation for special characters in Magento requires a targeted approach that focuses on the specific tables causing issues. By using the ALTER TABLE command to change the collation of the catalog_product_entity table and then reindexing the required actions, you can resolve sorting problems with Swedish characters without affecting the entire database.

Remember, this is a one-time fix for your current installation. If you encounter similar issues in future Magento versions or need to revert the changes, you can easily alter the collation back using the same SQL command.

For more detailed information on Magento’s database configuration and best practices, feel free to explore our guides at LaravelCompany. We provide comprehensive resources for developers looking to optimize their Magento installations.

Catalog Database Configuration Import/Export

Our network