MYSQL - Altering a Table Column to Accept Less Characters-CodePudding

I have a column in a table that I wish to limit to a maximum of 3 characters. The column is already populated with data upto 16 characters in the column.

If I change the column will it remove all the current data in the column or will it just keep the first 3 characters in each row when the column is changed?

Here is an example of the data in one of the rows of the column.

"uli4793mot"

I want to reduce the characters size limit of the column to only 3 allowed. So I would end up keeping "uli" in this particular row of the column. Which are the first 3 characters.

Is there an SQL statement that would reduce each row in the column to the first 3 characters?

CodePudding user response：

MySql default sql mode is strict, which prevents you from truncating (change column size) data accidentally by doing alters like the one you mentioned. However, if you disable strict mode with:

set sql_mode='';

You will be able to change column size and column text is truncated if length is greater than new column size.

CodePudding user response：

This will truncate the column to 3 characters before you alter:

UPDATE tablename SET columnname = LEFT(columnname, 3);