MySQL string sorting

topic description

the database has a list of name data as follows
* * Biography of Chuqiao Episode 1
Biography of Chuqiao Episode 2
.
Biography of Chuqiao Episode 10 * *

or
Biography of Chuqiao 02
Biography of Chuqiao 02
.
Biography of Chuqiao 11

how to sort by size habit

sources of topics and their own ideas

what I do now is customize a function get_num_from_char to extract consecutive numbers
and then order by get_num_from_char (name)
do my predecessors have any better solutions

related codes

originally intended to use a separate field such as sort to manage this sort
, but the user imported the list from a library and did not want to deal with it manually

Mysql

Mar.23,2022

as you said, write a Filter function get_num_from_char (name) to return a sequence number, and then save it to a field
for example, a very simple idea is to replace one, two, three with 123 , and then leave the number 0-9 .

XXX episode 1 to 1
XXXX-002 episode-HD to 002
season 1 episode 003 , to 1003
season 2 episode 004 , to 2004
season 3 episode 005 .1080P < / code

Save the results to a rank field, and then use this field for order by sorting as long as you keep the same series of names in a similar format

.
 complain,  sort  is a verb, database field, please use noun 
 in addition, you have seen that direct string sorting. It can only be said that imagination is beautiful. 
 actually becomes  1, 10, 11. 2, 20, 21. 3, 30, 31.  this 
  order by get_num_from_char (name) is not recommended because it will degenerate the index on name into a full index scan. 
 it is actually better to use a single field to deal with it. 
 
 originally thought that the default sorting could also be used, but ignored some cases, thanks to @ month Lord LM's reminder 
 it is better to add a new field. When inserting data, the value of this field is automatically set to the number of sets (automatically processed by the program), which is transparent to the user. It is very convenient to add fields either automatically sort or manually adjust the order later 
  it is recommended to add fields. While inserting data, the code processes the number of sets and inserts them into the corresponding fields 
.



Previous: How to download axios file?
Next: How to set csrf token to cookie by sinatra or patrino framework







Php mariadb sum divided by if there is no value, can NAN, be shown as 0?

$selectTip = $pdo->query("SELECT * FROM `tips_rate`
WHERE `tip_id` = ".$row[ id ]." "
);
$selecttotal = mysqli_num_rows($selectTip);
$rate = $pdo->query("SELEC...


Mysqli
mariadb
php


Feb.26,2021




What is the relationship between transactions at different levels of isolation?

for example, the isolation level of transaction An is repeatable read my understanding is that a read sql statement of transaction An is the same as the result repeated many times, regardless of whether or not other transactions have changed the data ...


Database
mysql


Feb.26,2021




About the problem of the sudden downmachine of the mysql master from one of the parties.

I know that mysql master and slave have been configured, but I don t know what to do if one of them dies:
1. If there is only one master and one slave, do you want to upgrade the master manually in case the master dies? (it seems that you can t call...


Read-write-separation
master-slave-backup
master-slave-replication
mysql


Feb.26,2021




SQL statement, JOIN multiple table join table query problem

subject table, which can be understood as a classification table. The course and source_book tables are associated with subject now use the subject table as the main table to query how many courses and books are under this category.
query statemen...


Mysql
sql


Feb.26,2021




Check-in function, data storage time, year, month, day, why use varchar

do a check-in function
`create_time` decimal(20,3) NOT NULL,
`create_date_ymd` varchar(15) NOT NULL COMMENT ,
`create_date_ym` varchar(15) NOT NULL,
`create_date_y` int(4) NOT NULL,
`create_date_m` int(2) NOT NULL,
`create_date_d` int(2) ...


Php
mysql


Feb.26,2021




Mysql fetches segmented data before the current time

as shown in the following figure
the current time is 16 41 00. I want to take this section of data every two hours. How should sql write ?
...


Mysql


Feb.27,2021




How mysql queries specified data in multiple tables

1. Problem description assume that there are n game order tables in the database, and the table name is game_ [0-9a-z] + _ order. . It is known that there is an order number 18demo000001. How can we efficiently find out which game table this order is i...


Mysql


Feb.27,2021




The optimization problem of group by sentence in mysql and how to find the train of thought

I want to optimize a sentence about group by. I don t know where to start. I want to find a train of thought .
SELECT
adv_id,
network_id,
SUM(re_click) AS clk,
SUM(deny_click) AS dny_clk,
SUM(re_conver) AS re_ins,
SUM(mat_conver) AS mat_ins,
SUM(def_...


Mysql
php


Feb.27,2021




On the Database query Optimization of java programmers in interview

background: 1. I am a java programmer 2. Recently interviewed for a senior development position 3. The previous project mainly used less oracle,mysql, and the companies interviewed basically used mysql s 4. I am weak in the database 5. Recently, I...


Oracle
mysql
sql
java


Feb.27,2021




Does mysql support whether the query condition is an object?
 like this SELECT * FROM material WHERE {key:value} limit 1 ...


Axios
node.js
express
php
mysql


Feb.27,2021




MySQL statement to ask for help

how does sql write from the database table to the display of the page?
requirement: the first-level classification needs sorting, the first-level classification shows its subcategories, and the subcategories also need sorting.
...


Sql
mysql


Feb.27,2021




The problem of computing sorting in mysql query

check-in table: fields include user id userid check-in time signtime timestamp type int check-in type type is divided into 0 and 1 for check-in and check-out, respectively. A user can sign twice a day, once check-in and one check-out. Check-in and ch...


Mysql


Feb.27,2021




Why can I still insert null values when not null, is set in mysql?

Table structure
...


Mysql


Feb.27,2021




Annotation invalidation of dao interface using mybatis @ CacheNamespace cache

use mybatis to configure custom secondary cache. Using xml annotation can use < cache type= "com.test.dao.MybatisRedisCache " > to successfully use custom cache, but using @ CacheNamespace (implementation = MybatisRedisCache.class) on the dao i...


Java
spring
mybatis
redis
mysql


Feb.27,2021




The correct limit and take in the Larave with () function

class essay extends Model
{
public function comment()
{
return $this->hasMany( App comment )
}
}
class comment extends Model
{
public function essay()
{
return $this->belongsTo( App essay )
}
}
...


Mysql
laravel


Feb.27,2021




The time-consuming problem of batch storage of a large amount of data in Mysql:, why does the speed decline so fast when the amount of data is large?

mybatis+mysql for java projects
int batchAmount = 20000;
for (int startIndex = 0; startIndex < size; startIndex += batchAmount) {
int endIndex = startIndex + batchAmount;
endIndex = (endIndex &...


Mysql
java


Feb.27,2021




Mysql master-slave server, master database build table slave database not Synchronize

Master and Slave built by mysql5.6, the newly created table is not backed up to the slave library master and slvae data can both correspond to
can t the test library be automatically backed up to the slave library when establishing a master-slav...


Linux
mysql


Feb.27,2021




PHP MYSQL loops to output a single piece of data sequentially

I want to do a circular sequence to output pictures. Don t know how to write this code?
Table structure
for example, now the field ID of key=1 is 1, 2, 5, 7, 10, 11, 6 fields
I want to output one at a time in order.
output id 1 field for t...


Php
mysql


Feb.27,2021




Is it convenient to modify data items when there is a lot of data in the mysql enum type?
 for example, if the database has a field type of enum ( axiomagementific database ), can you change this field to enum when there are millions of data in the database? will it be slow? ...


Mysql
sql


Feb.28,2021




Please help me convert this native SQL statement into TP5 thank you!
 ask God to help me translate this native SQL statement into TP5 thank you! It is Db:: ( name )-> this kind of chained operation select a.id, a.titlerect a. Createwriting time as answer_count from fa_question a. Isometric solvepaper count (b.qid) as ...


Thinkphp5
mysql
php


Feb.28,2021






False


Django, Why does the system automatically generate an empty session? every time the request is made?


Use python to convert dict1 to dict2, in the example. I tried for a long time but couldn't find a solution.


How to self-start the php-fpm service when building a LNMP environment with docker?


Can an array in react state be bound to a dom state using an element in the array?


Wechat browser version 6.6.5 cannot prevent the springback of pull-up and drop-down to the top under ios system.


In express+hbs, how do you define the routed data as a variable on the page?


How to use symbol reference of Iconfont in dva


How to elegantly add data to JSON objects?


Using node to build video streaming server


The proportional width of the echarts graph is inconsistent.


React uses axios to call the background data. After success, how to jump to the page based on the returned value?


I don't understand what a linux command means. Please explain it.


Vue introduces multiple components in vue-router by nesting named views, and does not refresh the same reference in other pages.


Running nodejs reports fewer errors ")"



css
mysql
arrays
josn
react
html
typescript
webpack
npm
sass
R
objective-c
.net
sql-server
jquery
python-3.x
angularjs
django
angular
excel
regex
iphone
ajax
linux
xml
pandas
vba
spring
database
wordpress
string
wpf
xcode
windows
bash
postgresql
oracle
multithreading
eclipse
list
firebase
algorithm
macos
forms
image
scala
visual-studio
azure
bootstrap
spring-boot
react-native
python-2.7
docker
performance
function
winforms
matlab
powershell
apache
dataframe
api
sqlite
numpy
rest
shell
selenium
flutter
dart
maven
loops
qt
swing
android-studio
csv
express
file
class
tensorflow
sorting
codeigniter
perl



Redis failed to set maxclients


Failed to publish the web project for Myescplise


How to make the jquery-weui pop-up layer scroll?


Can I delete the package under the pkgs file in anaconda?


Can String be converted to set in redis?




© 2021 CodesHelper


Advertising
Contact us
About us


Menu