Convert BD to utf8

Question

Convert BD to utf8

Asked 5 years, 1 month ago

Viewed 104 times

0

I have a mysql database which is from a very old project (~2009).

The accents saved in it are this way:

Beginning = Beginning
Location = Location§
Photo album = Albumen of photos

In the SQL file, it looks like this:

/*!40101 SET NAMES utf8 */;

And when you create the tables is like this:

CREATE TABLE IF NOT EXISTS `pagina_extra` (
`ID_PExtra` int(10) NOT NULL,
  `ordem_paginas` int(3) NOT NULL,
  `id_menu` int(255) NOT NULL,
  `url_seo` varchar(255) NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `nome` varchar(30) NOT NULL,
  `titulo` varchar(255) NOT NULL,
  `conteudo` longtext NOT NULL,
  `exibir` varchar(1) NOT NULL COMMENT '[S] Sim  [N] Não',
  `interno` varchar(1) NOT NULL COMMENT 'Página interna do sistema. [S]Sim [N]Nao'
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=77 ;

INSERT INTO `pagina_extra` (`ID_PExtra`, `ordem_paginas`, `id_menu`, `url_seo`, `url`, `nome`, `titulo`, `conteudo`, `exibir`, `interno`) VALUES
(2, 1, 1, 'inicio', 'index.php', 'InÃcio', '', '', 'S', 'N'),
(75, 3, 1, 'localizacao', 'pag_localizacao.php', 'LocalizaÃ§Ã£o', '', '', 'N', 'N'),
(76, 12, 1, 'album-de-fotos', 'pag_algum.php', 'ÃÂlbum de fotos', '', '', 'S', 'N');

Question:

What should I do to import this BD and turn these characters into normal accents like í, ç, ã, etc....

Thank you.

This problem may even be in your code editor in relation to the source file, considering that the accent is not "escaped" in Insert. There’s more than one problem there. You need to check the actual values stored in binary in the fields to see if it is a display or storage error.

– Bacco

2019/10/17 at 09:29
@Bacco yes, this was my first project in ~2009. I am redoing all the code, but I would like to take advantage of the same BD. Can you help me import this BD without these characters?. Thank you

– Tiago

2019/10/17 at 09:46
Are you using phpmyadmin?

– Guilherme Nascimento

2019/10/17 at 10:47
@Guilhermenascimento Yes, I am.

– Tiago

2019/10/17 at 10:50
1

This ai a problematic bank manager (phpmyadmin is a manager and not the bank), always failed to import, precisely with characters, I gave up on it a long time, I usually solve the import and export via command line, in this reply I did https://en.stackoverflow.com/a/51317/3635 read the part "How to backup without coding problems" and as a suggestion for maintenance and management could try the program: https://www.heidisql.com/

– Guilherme Nascimento

2019/10/17 at 10:58
@Guilhermenascimento, but currently I have not had problems with Phpmyadmin, I believe it was a mistake in the past, for lack of understanding and I ended up allowing the data rescue in the wrong way in the BD. I wonder if it is possible to revert this BD to the correct character format.

– Tiago

2019/10/17 at 11:01
The phpmyadmin at the time of exporting and importing "break" the characters, or better encode 2x, you may think not to have a problem with it, but surely it (phpmyadmin) has problems. I tell you because I have a good time of experience and I dropped it precisely because I had many headaches with this (apart from other problems that it is difficult to enumerate here). I managed many sites and banks and around and a half had to open Notepad++ to change the codec of . sql to fix, because of phpmyadmin

– Guilherme Nascimento

2019/10/17 at 11:07
@Guilhermenascimento understood. I will download Heidisql, with it I can import and convert these crazy characters into correct accents?

– Tiago

2019/10/17 at 11:14
Try to open . sql on Notepad++ and save as copy with windows-1252, then close and open the copy again . sql and make sure they are "correct".

– Guilherme Nascimento

2019/10/17 at 11:16
@Guilhermenascimento I’ll put him down and I’ll tell you. Thank you so far.

– Tiago

2019/10/17 at 11:18
@Guilhermenascimento got it. I was separating the tables that have the character problem and in Notepad++ I was alternating in ASCI and UTF-8 and it worked. Put your answer there to mark as solved.

– Tiago

2019/10/17 at 11:37
@Guilhermenascimento, can explain more about this copy file with windows-1252, I didn’t understand that win-1252...

– RXSD

2019/10/17 at 13:05
@Andréfilipe create a copy of yours. sql, open on Notepad++, go to the top menu where this written "Encoding", select "Convert to ANSI", save the doc and open again

– Guilherme Nascimento

2019/10/17 at 14:12
Ahhh yes, I knew this technique. It just wasn’t clear to me this term windows-1252. Has some relation this ANSI standard with the windows-1252?

– RXSD

2019/10/17 at 14:17

Show 9 more comments

2 answers

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.

by Antonio Dvino • 57 points · Answer 1 · 2019-10-17T05:35:00+00:00

If you want to avoid having to always enter in the code, you can switch directly in the database manager console:

ALTER DATABASE nome_banco DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

by Wellson Almeida • 16 points · Answer 2 · 2019-10-17T02:04:17+00:00

If using PDO, just add the array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")); so on the connection it shows normal. As the example below.

try {
    $pdo = new PDO("mysql:host=$hostname;dbname=$banco; charset=utf8", $usuario, $senha,
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

}