开发者

What does MySQL converting varchar to char?

开发者 https://www.devze.com 2023-03-21 20:22 出处:网络
When we add a new table, we made a mistake : a field was a varchar(255), but we have to write join queries.

When we add a new table, we made a mistake : a field was a varchar(255), but we have to write join queries. So we alter table to make a char(6).

My question is : what does MySQL in this case ? It trims rig开发者_如何学JAVAht ?


Rather than worry about what MySQL does or doesn't do why not just convert the data yourself before the change.

e.g.

UPDATE YourTable
SET YourField = LEFT(YourField,6);

ALTER TABLE YourTable MODIFY COLUMN YourField Char(6)

You should note that if your column data is too long it won't let you do the alter assuming enable strict SQL mode see Shef's complete answer


It will try to convert the format. In your case, the column is a string type, so it will truncate the value, if the strict SQL mode is not enabled.

When you change a data type using CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as well as possible.

Warning This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE (see Section 5.1.6, “Server SQL Modes”).

Read the fine warning down two fifth of the page.


MySQL won't even let you alter the table if there's any case you'll lose data (e.g. a row has an entry where the field's text is longer than six characters), so you're better off converting the data how you see fit.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号