• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

How to alter table in MySQL DB from created value?

I have an old and new database with different config as follows.

Old -`id` varchar(36) CHARACTER SET latin1 NOT NULL,

New - `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,

Can I alter the old to be like new and if so how?

Something like?:
  alter table networks id COLLATE utf8_unicode_ci;
0
mickt
Asked:
mickt
  • 4
  • 3
2 Solutions
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
You can use for the entire database
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
and for the table
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Remember to make a backup before!
0
 
micktAuthor Commented:
Thanks Ferruccio

Hit the following; think I may end up in a downward spiral shortly.

Cannot change column 'id': used in a foreign key constraint
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
Thats because there's a foreign key in another table referenced to a column from this, and there the column ( and so the key)  is expected as Latin1
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
micktAuthor Commented:
I guess there is no simple solution here and each has to be manually tweaked?
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
0
 
micktAuthor Commented:
Getting there; I'll look into this error tomorrow.

Warning: Using a password on the command line interface can be insecure.
ERROR 1118 (42000) at line 57: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 14000. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
0
 
micktAuthor Commented:
Thanks for your help Ferruccio.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now