我想使用contains函数获取title或question包含某个值的所有值。 但是,我收到此错误:
java.sql.SQLException: Geometry byte string must be little endian. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120) at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2076) at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1932) at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3426) at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:488) at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3131) at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2299) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2722) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322) at SQL.Select.getRecentQuestions(Select.java:447) at org.apache.jsp.Questions_jsp._jspService(Questions_jsp.java:198) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:111) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:411) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:473) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:377) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673) at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174) at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:416) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:283) at com.sun.enterprise.v3.services.impl.ContainerMapper$HttpHandlerCallable.call(ContainerMapper.java:459) at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:167) at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:206) at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:180) at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:235) at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119) at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:283) at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:200) at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:132) at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:111) at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77) at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:536) at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112) at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:117) at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:56) at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:137) at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:591) at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:571) at java.lang.Thread.run(Thread.java:748)
这是代码:
public String getRecentQuestions(String search) throws SQLException { PreparedStatement ps=con.prepareStatement("select title, question_id from questions where contains(title, ?) or contains(question, ?) limit 20"); ps.setString(1, search); ps.setString(2, search); ResultSet rs=ps.executeQuery(); StringBuilder sb=new StringBuilder(""); while (rs.next()) { sb.append(""); sb.append(rs.getString(1)); sb.append(""); } return sb.toString(); }
我的PreparedStatement有效,title列数据类型为varchar,并且question列数据类型也为varchar。
执行下面SQL异常:Geometry byte string must be little endian.
SELECT t.* FROM table_name t WHERE ST_CONTAINS ( t.the_geom, ST_GeomFromText ( 'POINT(119.65343619053 29.084639385514)' ))
mysql使用instr(str,substr)代替contains
也许您可以尝试使用instr
发表评论
2023-09-06 15:33:44回复
2023-09-06 15:32:00回复
此请求适用于 MariaDB (10.2)。
CREATE TABLE IF NOT EXISTS geo (
id INT AUTO_INCREMENT NOT NULL,
value GEOMETRY NOT NULL,
SPATIAL INDEX idx_value (value),
PRIMARY KEY(id)
) ENGINE = InnoDB;
INSERT INTO geo (value) SELECT ST_GeomFromText('LINESTRING(1 2)');
MySql (5.7.20) 上没有,错误是:
3037 - Invalid GIS data provided to function st_geometryfromtext.
在 MySql 中有 3 个函数来识别这样的几何图形:ST_IsSimple()、ST_IsValid() 和 ST_Validate() 但它们不' 处理格式错误的几何图形:
SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 2)'));
SELECT ST_IsValid(ST_GeomFromText('LINESTRING(1 2)'));
SELECT ST_AsText(ST_Validate(ST_GeomFromText('LINESTRING(1 1)')));
3055 - Geometry byte string must be little endian.
本例来自https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html ,但它不起作用。所以很奇怪(该文档未针对 5.7 更新)。有关 mysql 有效性的更多详细信息:https://dev.mysql.com/doc/refman/5.7/en/geometry-well-formedness-validity.html (mysql 接受任何语法上格式正确的输入,但不接受几何上无效的输入)。
这里有类似的问题
2023-09-06 15:31:16回复
Error number: 3055; Symbol: ER_GIS_DATA_WRONG_ENDIANESS; SQLSTATE: HY000
Message: Geometry byte string must be little endian.
。
错误说明: ER_GIS_DATA_WRONG_ENDIANESS(错误号3055)是MySQL服务器在处理GIS数据时发出的错误,表示传入的GIS数据不符合我们所期望的字节顺序。
常见案例
解决方法: 可以根据错误消息检查GIS数据的字节顺序,以确保它与MySQL所期望的字节顺序相同。如果仍然不能解决问题,可以尝试复制数据到另一个表,从而重新生成GIS索引,以便正确处理新数据。