数据库字符集UTF16不显示正确的字符 [英] Database charset UTF16 not showing proper character
问题描述
我通过将每个表的数据库和字符集的排序规则设置为utf16，在mysql中创建了一个数据库。
我想要utf16的原因是，我想存储所有类型的数学方程如ΦB= BA =BAcosθβ（事实上在ΦB中，B是上标）
不仅上述公式，而且数据库可能有任何种类的希腊字母显示为链接我试图插入数据通过读取microsoft excel文件
在执行查询之前一切似乎在java代码中是正确的，但在插入数据库后，它显示为？B = BA = BAcos？ ？
按照utf16支持的字符集 任何想法？ 数据库表模式 代码： 您需要确保从excel中读取的数据是正确的，您的表已正确创建，已正确设置，并且您的从excel读取数据并将其写入数据库的代码正常工作。 如果似乎没有问题，下一个值得怀疑的是，数据库中的数据可能是正常的，你的观察数据的方法是错误的。 具体来说，如果你使用mysql控制台命令，那么您需要确保控制台能够正确显示从您的数据库获取的unicode字段。 图形数据库管理工具通常不会遇到这样的缺陷，因此为什么不使用与MySQL一起提供的MySQL Workbench工具？ 这应该照顾你的unicode问题。 您仍然会遇到另一个问题：格式化。 上标和下标是unicode几乎没有支持的东西（你可以有几个上标数字和）因此，excel在单元格文本中使用特殊的格式来表示上标和下标。 不幸的是，当你读取单元格时，excel不会给出这种格式你做。这似乎是一个难以解决的问题，但似乎有一些解决方案在这里：从Excel单元格读取富文本 I created a database in mysql by setting collation of database and charset of each table as utf16 The reason I going for utf16 is , I wanted to store all kind of math equations like ΦB =B.A=BAcosθ β ( in fact in ΦB, B is superscript ) not only the above formula but database might have any kind of greek letters as shown link I tried to insert data by reading microsoft excel file
before executing query everything seems correct in java code but after inserting in database it shows as ?B =B.A=BAcos? ? As per utf16 supported charset Link above equation should look fine for me
but it is not. Any idea ? Database Table schema Code :
You need to make sure that the data you read from excel is correct, that your table has been created properly, that your connection has been setup properly, and that your code which reads data from excel and writes them to the database is working properly. If there seems to be no problem in all that, then the next thing to suspect is that perhaps the data in the database is just fine, and your means of observing the data is wrong. Specifically, if you use mysql console commands, then you need to be sure that the console is capable of properly displaying unicode fields fetched from your database. Graphical database management tools usually do not suffer from such drawbacks, so why not use the "MySQL Workbench" tool that ships together with MySQL? This should take care of your unicode issues. You will still have another problem: formatting. Superscripts and subscripts are something that unicode has very little support for, (you can have a few superscript numbers and that's it,) so excel uses special formatting within the cell text to represent superscript and subscript. Unfortunately, excel does not give this formatting to you when you read cells the way you do. It seems to be a hard problem to solve, but there appears to be some sort of solution here: Reading rich text from an Excel cell 这篇关于数据库字符集UTF16不显示正确的字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
mysql> SHOW CREATE TABLE公式;
+ ---------- + ---------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------- +
|表|创建表|
+ ---------- + ---------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------- +
|公式| CREATE TABLE`formulae`（
`formulaeid` int（11）NOT NULL AUTO_INCREMENT，
`formulae` text CHARACTER SET utf16，
`concept_conceptid` int（11）NOT NULL，
PRIMARY KEY（`formulaeid`），
KEY`fk_formulae_concept_idx`（`concept_conceptid`），
CONSTRAINT`fk_formulae_concept` FOREIGN KEY（`concept_conceptid`）REFERENCES`concept`（`conceptid`）ON DELETE CASCADE ON UPDATE NO ACTION
）ENGINE = InnoDB AUTO_INCREMENT = 15 DEFAULT CHARSET = utf16 COLLATE = utf16_unicode_ci |
+ ---------- + ---------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------- +
集合中的1行（0.00秒）
final static int CONCEPT_SHEET_NUMBER = 0;
final static int FORMULAE_SHEET_NUMBER = 1;
//读取xl文件
public void readProductArgExcel（File inputFile）throws IOException {
FileInputStream file = new FileInputStream（inputFile）;
XSSFWorkbook workBook = new XSSFWorkbook（file）;
boolean isError = false;
for（int sheetNumber = 0; sheetNumber< workBook.getNumberOfSheets（）; sheetNumber ++）{
XSSFSheet sheet = workBook.getSheetAt（sheetNumber）;
for（int i = 1; i <= sheet.getLastRowNum（）; i ++）{
isError = false;
行row = sheet.getRow（i）;
List< Object> columnValues = new ArrayList< Object>（）;
for（int j = 0; j< row.getPhysicalNumberOfCells（）; j ++）{
cell cell = row.getCell（j）;
if（！（cell == null））{
if（cell.getCellType（）== 0）{
Integer conceptId =（int）cell
.getNumericCellValue ;
columnValues.add（conceptId）;
}
if（cell.getCellType（）== 1）{
String rowString = cell
.getStringCellValue（）;
columnValues.add（rowString）;
}
switch（sheetNumber）{
case CONCEPT_SHEET_NUMBER：
break;
// XL文件有两个表，第二个表有几个公式
case FORMULAE_SHEET_NUMBER：{
if（j == 1）{
if（columnValues.size（）> = 2）
//这个xl表有第1列 - 公式，第2列 - 整数
insertFormulae（（String）columnValues.get（0），（Integer）columnValues.get（1）
}
break;
}
默认：
break;
}
}
}
}
System.out.println（sheet.getSheetName（））;
}
}
public static Connection getConnection（）throws InstantiationException，
IllegalAccessException，ClassNotFoundException，SQLException {
Connection connection = null;
/ *尝试但没有工作
jdbc：mysql：// localhost：3306 / formulaeDB？useUnicode = true& characterEncoding = utf16
完美地使用
jdbc：mysql：// localhost：3306 / formulaeDB？useUnicode = true& characterEncoding = utf8
* /
String connectionURL =jdbc：mysql：// localhost：3306 / formulaeDB;
Class.forName（com.mysql.jdbc.Driver）。newInstance（）;
connection = DriverManager.getConnection（connectionURL，root，root）;
/ *下面的代码抛出异常
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException：变量'character_set_client'不能设置为'utf16'的值
* /
String queryString =SET NAMES utf16;
PreparedStatement preparedStatement = connection.prepareStatement（
queryString，PreparedStatement.RETURN_GENERATED_KEYS）;
preparedStatement.executeUpdate（）;
返回连接;
}
//插入到表中
public static void insertFormulae（String formula，Integer conceptId）{
String queryString =INSERT INTO formula（formula，concept_conceptid ）VALUES（？，？）;
连接连接;
try {
connection = getConnection（）;
PreparedStatement preparedStatement = connection.prepareStatement（
queryString，PreparedStatement.RETURN_GENERATED_KEYS）;
preparedStatement.setString（1，formula）;
preparedStatement.setInt（2，conceptId）;
preparedStatement.executeUpdate（）;
} catch（InstantiationException | IllegalAccessException
| ClassNotFoundException | SQLException e）{
// TODO自动生成的catch块
e.printStackTrace
}
}
public static void main（String st []）throws IOException {
ExcelParser r = new ExcelParser（）;
r.readProductArgExcel（new File（
c：//data/Formulae.xlsx））;
}
mysql> SHOW CREATE TABLE formulae;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| formulae | CREATE TABLE `formulae` (
`formulaeid` int(11) NOT NULL AUTO_INCREMENT,
`formulae` text CHARACTER SET utf16,
`concept_conceptid` int(11) NOT NULL,
PRIMARY KEY (`formulaeid`),
KEY `fk_formulae_concept_idx` (`concept_conceptid`),
CONSTRAINT `fk_formulae_concept` FOREIGN KEY (`concept_conceptid`) REFERENCES `concept` (`conceptid`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf16 COLLATE=utf16_unicode_ci |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
final static int CONCEPT_SHEET_NUMBER = 0;
final static int FORMULAE_SHEET_NUMBER = 1;
//Reads xl file
public void readProductArgExcel(File inputFile) throws IOException {
FileInputStream file = new FileInputStream(inputFile);
XSSFWorkbook workBook = new XSSFWorkbook(file);
boolean isError = false;
for (int sheetNumber = 0; sheetNumber < workBook.getNumberOfSheets(); sheetNumber++) {
XSSFSheet sheet = workBook.getSheetAt(sheetNumber);
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
isError = false;
Row row = sheet.getRow(i);
List<Object> columnValues = new ArrayList<Object>();
for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
Cell cell = row.getCell(j);
if (!(cell == null)) {
if (cell.getCellType() == 0) {
Integer conceptId = (int) cell
.getNumericCellValue();
columnValues.add(conceptId);
}
if (cell.getCellType() == 1) {
String rowString = cell
.getStringCellValue();
columnValues.add(rowString);
}
switch (sheetNumber) {
case CONCEPT_SHEET_NUMBER:
break;
//XL file has two sheets, second sheet has few formulae
case FORMULAE_SHEET_NUMBER:{
if(j == 1){
if(columnValues.size() >= 2)
//this xl sheet has column 1 - formula, column2 - integer
insertFormulae((String)columnValues.get(0),(Integer)columnValues.get(1));
}
break;
}
default:
break;
}
}
}
}
System.out.println(sheet.getSheetName());
}
}
public static Connection getConnection() throws InstantiationException,
IllegalAccessException, ClassNotFoundException, SQLException {
Connection connection = null;
/*Tried with but didn't work
jdbc:mysql://localhost:3306/formulaeDB?useUnicode=true&characterEncoding=utf16
but it perfectly works with
jdbc:mysql://localhost:3306/formulaeDB?useUnicode=true&characterEncoding=utf8
*/
String connectionURL = "jdbc:mysql://localhost:3306/formulaeDB";
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection(connectionURL, "root","root");
/*The below code throws an exception
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Variable 'character_set_client' can't be set to the value of 'utf16'
*/
String queryString = "SET NAMES utf16";
PreparedStatement preparedStatement = connection.prepareStatement(
queryString, PreparedStatement.RETURN_GENERATED_KEYS);
preparedStatement.executeUpdate();
return connection;
}
//To insert into table
public static void insertFormulae(String formula,Integer conceptId){
String queryString = "INSERT INTO formulae(formulae,concept_conceptid) VALUES(?,?)";
Connection connection;
try {
connection = getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(
queryString, PreparedStatement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, formula);
preparedStatement.setInt(2, conceptId);
preparedStatement.executeUpdate();
} catch (InstantiationException | IllegalAccessException
| ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String st[]) throws IOException {
ExcelParser r = new ExcelParser();
r.readProductArgExcel(new File(
"c://data/Formulae.xlsx"));
}
