如何在mysql中更改字段类型并保持其值？ [英] How to change a field type in mysql and keep its value?

问题描述

我在MySQL数据库中有一个表，其中包含string（varchar（20））类型的字段。它的内容是时间类型，如23：30,14：00等。虽然它的长度是20，但只需要5个字符。无论如何，我更喜欢将其更改为int类型的字段，内容为0到47，表示00:00到23:30。但我需要保留客户数据库的内容。我可以简单地运行：

I've a table in MySQL database which contains a field of type string (varchar(20)). Its content is of type time, like 23:30, 14:00, etc. Although its length is 20, only 5 characters are required. Anyway, I prefer to change it to a field of type int with content 0 to 47 to indicate 00:00 to 23:30. But I need to keep contents of customers' databases. I may simply run:

alter table Schedules modify Start tinyint(1);

但是这会重置行中字段的内容。我需要一个MySQL脚本来完成它而不清除内容。也许我要添加一个中间栏。



我尝试过：



不知道如何将字段内容保存在行中。这是一个我不知道的问题。

But this resets contents of the field in rows. I need a MySQL script to do it without clearing the contents. Maybe I've to add an intermediate column.

What I have tried:

Don't know how to keep the field contents in rows. This is a question I've no clue about.

推荐答案

时间格式的值没有任何数字表示。因此，如果您转换列的类型，它将擦除干净无论它无法转换。相比之下，char类型表（例如，只是普通数字）将在列更改时进行转换。这是'它可以用新列类型表示吗？'。



最好的解决方案是创建一个int类型的新列，更新你的表，这样你将您的时间转换为int格式，然后删除时间列。



备选：在新列中使用计算列值并处理函数中的工作，以便您可以输入ab：cd格式，它将自动转换为备用列。

The values in time format don't have any numerical representation. Thus, if you convert the column's type it will wipe clean whatever it can't convert. By comparison, a table of char types, that are all, for example, just plain numbers, will convert when the column's changed. It's a matter of 'can it be represented in the new column type?'.

Best solution is to create an new column of type int, update your table so you convert your times to int format, and then drop the time column.

Alternative: use a computed column value in the new column and handle the work in a function so that you can enter ab:cd format and it will be converted in the alternate column automatically.

这篇关于如何在mysql中更改字段类型并保持其值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！