Accent error in mysql [duplicate]

All tables are with DEFAULT CHARSET = utf8 but no COLLATE defined, as available in the dump (example below).

CREATE TABLE 'adsense' (
  'id' int(11) NOT NULL DEFAULT '0',
  'codigo' mediumtext NOT NULL,
  'canal' varchar(500) NOT NULL,
  'local' varchar(10) NOT NULL,
  'publicado' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Back up the database and remove everything from the current DB, then run the following command:

ALTER DATABASE 'ConteudoAnimal' CHARSET = UTF8 COLLATE = utf8_general_ci;

After this do the process of creating the tables and importing the data again. Several data that are in the current export are already "spoiled" because they were inserted with the wrong encoding, so they will not be used to insert into the tables.

See for example that the News table has the correct collate in the varchar field and it has the data without any encoding problem. An item by item scan should be done.

Use this:

 ALTER DATABASE 'sua_base' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

It will only change your database to UTF8, and this does not define anything or solve anything, there are multiple types of charset and probably you have to see which is the ideal for you, for example the most used are UTF- 8 and Latin1 and within these 2 there are still variations in mysql, see more details at:

• Which UTF-8 collate is most appropriate for Web (multi-language)

The problem of the characters

Problems with characters such as é or � occur due to bad database configuration up to your asp (or php) scripts, then is not a bank error , but rather an error understanding how to work with it.

If you are going to use UTF8 in the bank you should:

• Save ALL scripts as UTF-8 without BOM
• Set the charset while connecting to the bank
• Define the HTTP header in the request response such as Content-Type: text / html; charset = utf8

If you are going to use Latin1 in the bank you should:

• Save ALL scripts as ANSI (or ISO-8859-1 or window-1252)
• Set the charset while connecting to the bank
• Define the HTTP header in the request response such as Content-Type: text/html; charset=ISO-8859-1 (or Content-Type: text/html; charset=windows-1252 )

  

Attention, for it to work you must follow all the steps:

     

• Choose the encoding that will suit you best, latin1 or utf8
  
• Set the connection correctly
  
• Set http charset with the same charset as
  
• Save all documents / script without exception that will use bank data with the same encoding
  

Configuring UTF-8

You should save all ASP scripts (even those that you will use with <!--#include file="..."--> ) to utf-8 without "BOM", you can use softwares like SublimeText or notepad ++ to convert the files:

• Using notepad ++:

  

• UsingSublimeText:

Then you must set the connection to use UTF8 using the command SET CHARACTER SET utf8 :

  

If it is already configured in the file my.ini this may be expendable, but I would keep to avoid problems if someone edit the my.ini

Dim cnnSimple  ' ADO connection
Dim rstSimple  ' ADO recordset
Set cnnSimple = Server.CreateObject("ADODB.Connection")     

cnnSimple.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=[mySQL server];
DATABASE=[DBName];UID=[DBUser];PASSWORD=[DB Password];"

Set rstSimple = cnnSimple.Execute("SET CHARACTER SET utf8") 'Define o charset

Setting up latin1

First you should save "all .asp scripts" as iso-8859-1 (or ANSI):

• To save using notepad ++:

  

• TosaveusingSublimeText:

  

ThenyoumustsettheconnectiontouseUTF8usingthecommandSETCHARACTERSETlatin1:

  

Ifitisalreadyconfiguredinthefilemy.inithismaybeexpendable,butIwouldkeeptoavoidproblemsifsomeoneeditthemy.ini

Setting the HTTP charset

You can do this in two ways, via HTML or via response , below:

• Using UTF-8 in HTML

  <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  

• Using UTF-8 in response that should go before any HTML content:

  <%@Language=VBScript CodePage = 65001%>
  <%
  Response.charset ="utf-8"
  Response.CodePage = 65001
  %>
  <html>
  ...
  

• Using latin1 (iso-8859-1 or windows-1252) in HTML

  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
  

• Using UTF-8 in response that should go before any HTML content:

  <%@Language=VBScript CodePage = 65001%>
  <%
  Response.charset ="iso-8859-1" 'pode usar windows-1252 também
  Response.CodePage = 28591
  %>
  <html>
  ...
  

Conclusion

If you have followed all the steps correctly, there will probably be no more failures, neither reading the bank nor writing the data, all that has been said in this answer is the same as I have already explained in

• Doubt with charset = iso-8859-1 and utf8 (see answer)

Use the following command on the tables that are giving trouble with accent.

 ALTER TABLE 'sua_tabela' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

This is because in the Charset defined for your MySQL does not have this type of accent. With the command above you will use the Latin charset, which has these characters.

Or you can also change every database with the following command

ALTER DATABASE 'sua_base' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;