Remove UNSIGNED When Changing a MySQL Integer Column to VARCHAR

When an integer column becomes VARCHAR, its numeric UNSIGNED attribute must disappear. Some administration interfaces can carry that old attribute into the generated statement, producing invalid SQL. Review the full new definition and run a corrected ALTER TABLE yourself.

Last updated: November 20, 2026.

SHOW CREATE TABLE ps_mail_address;

ALTER TABLE ps_mail_address
  MODIFY COLUMN id_customer VARCHAR(20) NULL DEFAULT NULL;

This example keeps the column name and changes only its definition. Adjust the length, nullability, character set and default to match the application. If the column is indexed or referenced by a foreign key, review those dependencies before altering it.

Why the generated statement fails

UNSIGNED belongs to numeric column definitions; it is not part of MariaDB’s documented VARCHAR syntax. A current phpMyAdmin report shows version 5.2.0 generating VARCHAR(20) UNSIGNED while changing an integer field on MariaDB 10.4.27. Remove the stale numeric attribute instead of trying to preserve every checkbox from the old type.

The report does not establish that every phpMyAdmin version has the same problem. Inspect the SQL preview before accepting a structural change, especially when switching between numeric and string families.

Rebuild the complete column definition

MySQL’s ALTER TABLE documentation explains that MODIFY and CHANGE use a complete new column definition. Attributes omitted from that definition are not automatically carried forward. Start with SHOW CREATE TABLE, then decide deliberately which nullability, default, comment, character set and collation should remain.

Check the current values before conversion. Numeric values become text, which changes sorting and comparison behavior: for example, 10 sorts before 2 as text. Test the statement on a copy or take a recoverable backup, verify row counts and representative values afterward, and update application parameters so identifiers stored as text are passed as strings. MariaDB’s ALTER TABLE reference also documents MODIFY COLUMN for changing a column type.

Schedule the change with the table size and traffic in mind. A type conversion can rebuild or lock a table depending on the server version, storage engine and requested algorithm. Check the execution plan for the DDL and rehearse the rollback before changing a busy production table.

Related Web Cheat Sheet guides

Sergey Kornilov

Sergey Kornilov