将大价格值与小数点一起存储在SQLite数据库中 [英] Store Big Price value in database along with Decimal Point in SQLite Database
问题描述
可能是我的问题很愚蠢，但是我没有找到任何解决方案.实际上，我想存储大额价格
May be my question is silly, But I didn't find any Solution for this. Actually, I want to Store Big Price Amount i.e.
9999999999.99
9999999999.99
在Sqlite数据库中
并想取回它.我尝试使用TEXT，DOUBLE，REAL，LONG数据类型.
in Sqlite Database and want to retrive it back. I tried with TEXT, DOUBLE, REAL, LONG Datatypes.
不幸的是，当我这样做时，它返回我不需要的值，即9.999999999.如果您对此有解决方案，请提出建议或指导我.
Unfortunately when i do this, it returns me back unwanted value i.e. 9.999999999.. . Please suggest me if you have solution for this, or guide me.
推荐答案
已定义的列类型相似性 TEXT ， REAL ， NUMERIC ， VARCHAR ， RUMPLESTILTSKIN (是有效的)，影响不大；例外情况是类型是 INTEGER PRIMARY KEY 还是 INTEGER PRIMARY KEY AUTOINCREMENT ，在这种情况下，该列是SQLITE列 rowid 的别名.并且无法使用. ROWID和整数主键
The defined column type affinity TEXT, REAL, NUMERIC, VARCHAR, RUMPLESTILTSKIN (yes it works), has little impact; the exception being if the type is either INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT in which case the column is an alias of the SQLITE column rowid and cannot be used. ROWIDs and the INTEGER PRIMARY KEY
基本上，除rowid之外，任何列都可以按照以下条件存储任何类型的值
Basically any column, except a rowid, can store any type of value as per
SQLite版本3数据库中的任何列，但INTEGER PRIMARY除外KEY列可用于存储任何存储类的值. SQLite版本3中的数据类型
该链接还说明了如何将列类型转换为5种列类型之一:-
The link also explains how column types are converted to one of the 5 column types :-
- TEXT，
- INTEGER，
- 真实，
- BLOB或
- NUMERIC.
RuMpleStilskin(不区分大小写)将被转换为NUMERIC，具体如下:-
RuMpleStilskin (case independent) will be converted to NUMERIC, as per :-
- (a)它不包含 INT (如果具有INTEGER相似性)
- (b)它不包含 TEXT ， CHAR 或 CLOB (如果这样的话，TEXT亲缘关系，除非事先处理过)
- (c)它不包含 BLOB (如果如此，则BLOB亲和性除非事先处理)
- (d)它不包含 REAL ， FLOA 或 DOUB (如果这样，除非事先处理，否则为REAL亲和力)
- (e)如果上述 NUMERIC 都不是<<<<Rumplestilskin来到这里.
- (a) it doesn't contain INT (if so INTEGER affinity)
- (b) it doesn't conatain TEXT, CHAR or CLOB (if so TEXT affinity unless previously handled)
- (c) it doesn't contain BLOB (if so BLOB affinity unless previously handled)
- (d) it doesn't contain REAL, FLOA, or DOUB (if so REAL affinity unless previously handled)
- (e) if none of the above NUMERIC <<<< Rumplestilskin gets here.
最重要的是如何检索数据，尽管列的亲和力会影响按照以下方式存储数据(存储类)的方式:-
What is of primary importance is how you retrieve the data, although the column's affinity can affect how the data is stored (the storage class) as per :-
具有TEXT关联性的列使用存储类存储所有数据NULL，TEXT或BLOB.如果将数值数据插入到带有TEXT关联性，在存储之前会转换为文本形式.
A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.
具有NUMERIC关联性的列可能包含使用全部五个值的值存储类.将文本数据插入NUMERIC列时，文本的存储类将转换为INTEGER或REAL(按偏好)，如果这种转换是无损且可逆的.为了SQLite认为TEXT和REAL存储类之间的转换如果前15个，转化将是无损且可逆的该数字的有效十进制数字将被保留.如果不可能将TEXT无损转换为INTEGER或REAL该值是使用TEXT存储类存储的.没有尝试转换NULL或BLOB值.
A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values.
字符串可能看起来像带小数点的浮点文字和/或指数表示法，但只要该值可以表示为整数，NUMERIC关联会将其转换为整数.因此，字符串"3.0e + 5"存储在具有NUMERIC关联性的列中为整数300000，而不是浮点值300000.0.
A string might look like a floating-point literal with a decimal point and/or exponent notation but as long as the value can be expressed as an integer, the NUMERIC affinity will convert it into an integer. Hence, the string '3.0e+5' is stored in a column with NUMERIC affinity as the integer 300000, not as the floating point value 300000.0.
使用INTEGER相似性的列的行为与具有NUMERIC亲和力.INTEGER和NUMERIC相关性之间的区别仅在CAST表达式中可见.
A column that uses INTEGER affinity behaves the same as a column with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression.
具有REAL亲和力的列的行为类似于具有NUMERIC的列亲和力，不同之处在于它会强制将整数值转换为浮点数表示.(作为内部优化，浮点较小没有小数部分的值，并存储在带有REAL的列中亲和力以整数形式写入磁盘，以减少占用空格，并自动转换回浮点作为值被读出.这种优化是完全看不见的SQL级别，只能通过检查数据库文件.)
A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. (As an internal optimization, small floating point values with no fractional component and stored in columns with REAL affinity are written to disk as integers in order to take up less space and are automatically converted back into floating point as the value is read out. This optimization is completely invisible at the SQL level and can only be detected by examining the raw bits of the database file.)
具有亲和力BLOB的列不喜欢一个存储类另一个，并且没有尝试强制一个存储类中的数据变成另一个. SQLite版本3中的数据类型
A column with affinity BLOB does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another. Datatypes In SQLite Version 3
对于浮点数或双精度数，应使用 Cursor 方法 getFloat 或 getDouble 检索数据，然后格式化结果(如果将其转换)相应地转换为字符串. 注意！这是假设使用double或float的精度缺陷/损失不相关. 如果将值存储为字符串，并且列亲和力为TEXT或BLOB，则结果是无损失的，并且可能是存储大(请参阅结果确定大)数字的最佳方法.
For floating point or double you should retrieve the data using the Cursor methods getFloat or getDouble and then format the result, if converting it to a string, accordingly.
Note! this assumes that the flaws/loss of precision with double or float are not relevant.
If you store the value as a string and the column affinity is TEXT or BLOB then the result is loss-less and is perhaps the best way to store large (see results to determine what is large) numbers.
考虑一个包含5个列的表，这些列定义为列类型/关联性 REAL ， TEXT ， INTEGER ， NUMERIC 和 BLOB
Consider a table with 5 columns defined with column types/affinities REAL, TEXT, INTEGER, NUMERIC and BLOB
按照:-
CREATE TABLE prices (
_id INTEGER PRIMARY KEY,
PRICE1 REAL,
PRICE2 TEXT,
PRICE3 INTEGER,
PRICE4 NUMERIC,
PRICE5 BLOB
)
- "ID"列不重要.
现在考虑这3种基本方法-a)在所有5个PRICE中插入一个双精度值?列-b)在所有5个PRICE中插入一个字符串值?列-c)获取所有行作为游标:-
Now consider these 3 basic methods to - a) insert a double value into all 5 PRICE? columns - b) insert a string value into all 5 PRICE? columns - c) get all rows as a cursor :-
public void insertRowFromDouble(double price) {
ContentValues cv = new ContentValues();
cv.put(PRICES_PRICE1_COL,price);
cv.put(PRICES_PRICE2_COL,price);
cv.put(PRICES_PRICE3_COL,price);
cv.put(PRICES_PRICE4_COl,price);
cv.put(PRICES_PRICE5_COL,price);
mDB.insert(TBNAME,null,cv);
}
public void insertRowFromString(String price) {
ContentValues cv = new ContentValues();
cv.put(PRICES_PRICE1_COL,price);
cv.put(PRICES_PRICE2_COL,price);
cv.put(PRICES_PRICE3_COL,price);
cv.put(PRICES_PRICE4_COl,price);
cv.put(PRICES_PRICE5_COL,price);
mDB.insert(TBNAME,null,cv);
}
public Cursor getAllRows() {
return mDB.query(TBNAME,null,null,null,null,null,null);
}
，然后考虑以下代码:-
and then consider the following code, which :-
- a)创建一个DecimalFormat
- b)实例化Databasehelper
- c)删除所有现有行(出于重新运行的目的)
- d)使用insertRowfromDouble方法插入5行，这些行具有不同的值，即使每行具有不同的列类型，每行也将全部5列填充为相同的值.
- e)与d)相同，但使用insertRowFromString方法，并且使用不同但接近的值.
- f)获取所有10行的游标.
- g)使用根据
DecimalFormat设置格式的getDouble方法，然后使用getString方法格式化的游标，在光标中循环输出结果数据./li>
- a) Creates a DecimalFormat
- b) Instantiates a Databasehelper
- c) Deletes all existing rows (for rerun-ability)
- d) Inserts 5 rows using the insertRowfromDouble method with varying values, each row having all 5 columns populated with the same value even though they each have a different column type.
- e) does the same as d) but using the insertRowFromString method and within different but close values.
- f) obtains a cursor with all 10 rows.
- g) loops through the cursor output the resultant data using the
getDoublemethod formatted according to theDecimalFormatand then thegetStringmethod.
:-
final DecimalFormat df = new DecimalFormat("#0.000000");
mDBPricesHlpr = new PriceDBHelper(this);
mDBPricesHlpr.getWritableDatabase().delete(PriceDBHelper.TBNAME,null,null);
mDBPricesHlpr.insertRowFromDouble(999999999999999999999999999999999999999.99);
mDBPricesHlpr.insertRowFromDouble(12345678901.123456);
mDBPricesHlpr.insertRowFromDouble(9999999999.99);
mDBPricesHlpr.insertRowFromDouble(0.01);
mDBPricesHlpr.insertRowFromDouble(45678.45);
mDBPricesHlpr.insertRowFromString("999999999999999999999999999999999999999999999999999999999999999999999999999.99");
mDBPricesHlpr.insertRowFromString("12345678901.123456");
mDBPricesHlpr.insertRowFromString("8888888888.88");
mDBPricesHlpr.insertRowFromString("0.02");
mDBPricesHlpr.insertRowFromString("56789.56");
Cursor csr = mDBPricesHlpr.getAllRows();
while (csr.moveToNext()) {
Log.d("PRICEINFO_GETDBL"," Processing Row " + csr.getPosition() +
"\n\tPRICE1=" + df.format(csr.getDouble(csr.getColumnIndex(PriceDBHelper.PRICES_PRICE1_COL))) +
"\n\tPRICE2=" + df.format(csr.getDouble(csr.getColumnIndex(PriceDBHelper.PRICES_PRICE2_COL))) +
"\n\tPRICE3=" + df.format(csr.getDouble(csr.getColumnIndex(PriceDBHelper.PRICES_PRICE3_COL))) +
"\n\tPRICE4=" + df.format(csr.getDouble(csr.getColumnIndex(PriceDBHelper.PRICES_PRICE4_COl))) +
"\n\tPRICE5=" + df.format(csr.getDouble(csr.getColumnIndex(PriceDBHelper.PRICES_PRICE5_COL)))
);
Log.d("PRICEINFO_GETSTR", " Processing Row " + csr.getPosition() +
"\n\tPRICE1=" + csr.getString(csr.getColumnIndex(PriceDBHelper.PRICES_PRICE1_COL)) +
"\n\tPRICE2=" + csr.getString(csr.getColumnIndex(PriceDBHelper.PRICES_PRICE2_COL)) +
"\n\tPRICE3=" + csr.getString(csr.getColumnIndex(PriceDBHelper.PRICES_PRICE3_COL)) +
"\n\tPRICE4=" + csr.getString(csr.getColumnIndex(PriceDBHelper.PRICES_PRICE4_COl)) +
"\n\tPRICE5=" + csr.getString(csr.getColumnIndex(PriceDBHelper.PRICES_PRICE5_COL))
);
}
结果输出为:-
01-11 10:39:30.626 3680-3680/? D/PRICEINFO_GETDBL: Processing Row 0
PRICE1=1000000000000000000000000000000000000000.000000
PRICE2=1000000000000000000000000000000000000000.000000
PRICE3=1000000000000000000000000000000000000000.000000
PRICE4=1000000000000000000000000000000000000000.000000
PRICE5=1000000000000000000000000000000000000000.000000
01-11 10:39:30.626 3680-3680/? D/PRICEINFO_GETSTR: Processing Row 0
PRICE1=1e+39
PRICE2=1.0e+39
PRICE3=1e+39
PRICE4=1e+39
PRICE5=1e+39
01-11 10:39:30.626 3680-3680/? D/PRICEINFO_GETDBL: Processing Row 1
PRICE1=12345678901.123500
PRICE2=12345678901.123500
PRICE3=12345678901.123500
PRICE4=12345678901.123500
PRICE5=12345678901.123500
01-11 10:39:30.626 3680-3680/? D/PRICEINFO_GETSTR: Processing Row 1
PRICE1=1.23457e+10
PRICE2=12345678901.1235
PRICE3=1.23457e+10
PRICE4=1.23457e+10
PRICE5=1.23457e+10
01-11 10:39:30.626 3680-3680/? D/PRICEINFO_GETDBL: Processing Row 2
PRICE1=9999999999.990000
PRICE2=9999999999.990000
PRICE3=9999999999.990000
PRICE4=9999999999.990000
PRICE5=9999999999.990000
01-11 10:39:30.626 3680-3680/? D/PRICEINFO_GETSTR: Processing Row 2
PRICE1=1e+10
PRICE2=9999999999.99
PRICE3=1e+10
PRICE4=1e+10
PRICE5=1e+10
01-11 10:39:30.626 3680-3680/? D/PRICEINFO_GETDBL: Processing Row 3
PRICE1=0.010000
PRICE2=0.010000
PRICE3=0.010000
PRICE4=0.010000
PRICE5=0.010000
01-11 10:39:30.630 3680-3680/? D/PRICEINFO_GETSTR: Processing Row 3
PRICE1=0.01
PRICE2=0.01
PRICE3=0.01
PRICE4=0.01
PRICE5=0.01
01-11 10:39:30.630 3680-3680/? D/PRICEINFO_GETDBL: Processing Row 4
PRICE1=45678.450000
PRICE2=45678.450000
PRICE3=45678.450000
PRICE4=45678.450000
PRICE5=45678.450000
01-11 10:39:30.630 3680-3680/? D/PRICEINFO_GETSTR: Processing Row 4
PRICE1=45678.4
PRICE2=45678.45
PRICE3=45678.4
PRICE4=45678.4
PRICE5=45678.4
01-11 10:39:30.630 3680-3680/? D/PRICEINFO_GETDBL: Processing Row 5
PRICE1=1000000000000000000000000000000000000000000000000000000000000000000000000000.000000
PRICE2=1000000000000000000000000000000000000000000000000000000000000000000000000000.000000
PRICE3=1000000000000000000000000000000000000000000000000000000000000000000000000000.000000
PRICE4=1000000000000000000000000000000000000000000000000000000000000000000000000000.000000
PRICE5=1000000000000000000000000000000000000000000000000000000000000000000000000000.000000
01-11 10:39:30.630 3680-3680/? D/PRICEINFO_GETSTR: Processing Row 5
PRICE1=1e+75
PRICE2=999999999999999999999999999999999999999999999999999999999999999999999999999.99
PRICE3=1e+75
PRICE4=1e+75
PRICE5=999999999999999999999999999999999999999999999999999999999999999999999999999.99
01-11 10:39:30.630 3680-3680/? D/PRICEINFO_GETDBL: Processing Row 6
PRICE1=12345678901.123500
PRICE2=12345678901.123500
PRICE3=12345678901.123500
PRICE4=12345678901.123500
PRICE5=12345678901.123500
01-11 10:39:30.630 3680-3680/? D/PRICEINFO_GETSTR: Processing Row 6
PRICE1=1.23457e+10
PRICE2=12345678901.123456
PRICE3=1.23457e+10
PRICE4=1.23457e+10
PRICE5=12345678901.123456
01-11 10:39:30.630 3680-3680/? D/PRICEINFO_GETDBL: Processing Row 7
PRICE1=8888888888.880000
PRICE2=8888888888.880000
PRICE3=8888888888.880000
PRICE4=8888888888.880000
PRICE5=8888888888.880000
01-11 10:39:30.630 3680-3680/? D/PRICEINFO_GETSTR: Processing Row 7
PRICE1=8.88889e+09
PRICE2=8888888888.88
PRICE3=8.88889e+09
PRICE4=8.88889e+09
PRICE5=8888888888.88
01-11 10:39:30.630 3680-3680/? D/PRICEINFO_GETDBL: Processing Row 8
PRICE1=0.020000
PRICE2=0.020000
PRICE3=0.020000
PRICE4=0.020000
PRICE5=0.020000
01-11 10:39:30.634 3680-3680/? D/PRICEINFO_GETSTR: Processing Row 8
PRICE1=0.02
PRICE2=0.02
PRICE3=0.02
PRICE4=0.02
PRICE5=0.02
01-11 10:39:30.634 3680-3680/? D/PRICEINFO_GETDBL: Processing Row 9
PRICE1=56789.560000
PRICE2=56789.560000
PRICE3=56789.560000
PRICE4=56789.560000
PRICE5=56789.560000
01-11 10:39:30.634 3680-3680/? D/PRICEINFO_GETSTR: Processing Row 9
PRICE1=56789.6
PRICE2=56789.56
PRICE3=56789.6
PRICE4=56789.6
PRICE5=56789.56
所以:-
-
对于使用getDouble的第0行(通过double存储的999999999999999999999999999999999999999.99)，列类型无关紧要，但对于getString，当列类型为TEXT时会有细微的差别.但是，精度会失去价值(请参见上面的注意事项).
For row 0 (999999999999999999999999999999999999999.99 stored via a double) using getDouble the column type is insignificant, but for getString there is a subtle difference when the column type is TEXT. However, precision loses the value (see caveat above).
对于第1行(通过double存储的12345678901.123500)，getDouble适用于15个有效数字.使用getString时，除TEXT列之外的所有列都会由于精度损失而丢失.
For row 1 (12345678901.123500 stored via a double) getDouble is fine for 15 significant figures. With getString all but the TEXT column lose out due to precision loss.
第2行(通过双精度存储的9999999999.99)与第1行的发现非常相似.
For row 2 (9999999999.99 stored via a double) is very much the same findings as for row 1.
对于第3行(0.01通过双精度存储)是可以的.
For row 3 (0.01 stored via a double) is fine.
对于第4行(通过double存储的45678.45)，精度损失是getString的问题，除非它是TEXT列.
For row 4 (45678.45 stored via double) precision loss is an issue for getString except when it is a TEXT column.
对于第5-9行，数据是通过字符串存储的，TEXT和BLOB列在所有情况下均精确存储了该值，而没有精度损失.否则结果是相似的.
For rows 5-9 the data is stored via a string, TEXT and BLOB columns accurately store the value in all cases with no precision loss. Otherwise the results are similar.
这篇关于将大价格值与小数点一起存储在SQLite数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
