why does my sql table treat 1000 as a middle number?-CodePudding

When I try to sort by a value descending my SQL table does it correctly, but if it sees for example "1000" it always puts it in the middle? for Example:

this even happens when I reference it in spigot (I'm using it for a plugin) it outputs it the same way

this is how I'm calling it in my plugin:

PreparedStatement statement = database.getConnection().prepareStatement("SELECT uuid FROM player_stats ORDER BY blocks_broken DESC");
                        ResultSet rs = statement.executeQuery();
                        while (rs.next()) {
                            String name = rs.getString("uuid");
                            LeaderboardCommand.name = name;
                            String player = String.valueOf(Bukkit.getPlayer(UUID.fromString(name)));
                            p.sendMessage(player);

I know it's not perfect as I'm just learning/experimenting with databases currently, but I'm mainly asking for help on why the SQL is outputted this way & advice on any severe mistakes I'm making is greatly appreciated! Thanks in advance -Occy

public void createPlayerStats(PlayerStats playerStats) throws SQLException {

    PreparedStatement statement = getConnection()
            .prepareStatement("INSERT INTO player_stats(uuid, blocks_broken, last_login, last_logout) VALUES (?, ?, ?, ?)");
    statement.setString(1, playerStats.getPlayerUUID());
    statement.setLong(2, playerStats.getBlocksBroken());
    statement.setDate(3, new Date(playerStats.getLastLogin().getTime()));
    statement.setDate(4, new Date(playerStats.getLastLogout().getTime()));

    statement.executeUpdate();

    statement.close();

CodePudding user response：

It happens because block_broken type is a varchar and not a number.

In this case you are ordering lexycographically and not numerically.

You can change your query to handle that as a numeric value with an explicit cast so your query should be:

 SELECT uuid FROM player_stats ORDER BY cast(blocks_broken as numeric) DESC

Update: In MariaDb try to use this (You can try directly in the db client and once it is working update your java code):

 SELECT uuid FROM player_stats ORDER BY CAST(blocks_broken AS INTEGER) DESC